MSSQL数据库备份与恢复：编写代码实现自动备份

介绍

MSSQL是一种优秀的关系型数据库，备份和恢复是数据库中非常关键的两个操作。对于数据库管理员，自动备份程序可以减轻他们的工作负担，减少出错的几率。本文将介绍如何使用编写代码实现自动备份MSSQL数据库。

步骤

1. 确认备份目录位置

在进行自动备份之前，需要先确认备份文件需要存放到哪个目录下。可以将备份文件放到数据库服务器的本地硬盘上，也可以放到网络驱动器、远程FTP、SFTP等其他位置。本文将备份文件存放到本地硬盘上的D:\Backup\目录下。

2. 创建备份脚本

可以使用SQL Server Management Studio（SSMS）来创建备份脚本。打开SSMS，选择需要备份的数据库，然后右键菜单选择“任务”->“备份”。

在备份选项页面，设置“备份类型”为“完整”，选择备份文件存放路径、备份文件名、备份描述等信息。最后点击“确定”即可生成备份脚本。

下面是备份脚本的示例代码：

BACKUP DATABASE [YourDatabase] TO  DISK = N'D:\Backup\YourDatabase.bak' WITH NOFORMAT, NOINIT,  NAME = N'YourDatabase-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

请将脚本中的“YourDatabase”改为你的数据库名称，“D:\Backup\YourDatabase.bak”改为你的备份目录。

3. 创建自动备份脚本

使用SQL Server的代理服务可以创建自动备份脚本。打开SQL Server代理，右键菜单选择“新建作业”。

在作业属性页面，输入作业名称和描述信息。选择“步骤”，然后点击“新建”按钮，输入一个步骤名称，将备份脚本粘贴到“T-SQL”编辑器中，然后点击“确定”保存。

在“时间表”页面，设置备份计划。可以设置为每天备份一次、每周备份一次或者每月备份一次。设置完成之后，保存作业即可。

下面是自动备份脚本的示例代码：

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'YourJobName', 




@enabled=1, 




@notify_level_eventlog=0, 




@notify_level_email=0, 




@notify_level_netsend=0, 




@notify_level_page=0, 




@delete_level=0, 




@description=N'YourJobDescription', 




@category_name=N'[Uncategorized (Local)]', 




@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup]    Script Date: 4/1/2022 4:22:07 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup', 




@step_id=1, 




@cmdexec_success_code=0, 




@on_success_action=1, 




@on_success_step_id=0, 




@on_fail_action=2, 




@on_fail_step_id=0, 




@retry_attempts=0, 




@retry_interval=0, 




@os_run_priority=0, @subsystem=N'TSQL', 




@command=N'BACKUP DATABASE [YourDatabase] TO  DISK = N''D:\Backup\YourDatabase.bak'' WITH NOFORMAT, NOINIT,  NAME = N''YourDatabase-完整 数据库 备份'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
', 




@database_name=N'master', 




@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyBackup_Schedule', 




@enabled=1, 




@freq_type=4, 




@freq_interval=1, 




@freq_subday_type=1, 




@freq_subday_interval=0, 




@freq_relative_interval=0, 




@freq_recurrence_factor=0, 




@active_start_date=20220402, 




@active_end_date=99991231, 




@active_start_time=0, 




@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

请将脚本中的“YourDatabase”改为你的数据库名称，“D:\Backup\YourDatabase.bak”改为你的备份目录，“YourJobName”和“YourJobDescription”改为你的作业名称和描述信息。此脚本可以设置每天备份一次。

总结

通过以上步骤，我们可以创建一个自动备份的作业，可以有效地减轻数据库管理员的工作负担，并减少出错的几率。备份操作十分关键：如果没有及时且完整的备份，会带来不可挽回的后果。因此，备份是每个数据库管理员所必须学会的基本技能之一。