SQL Server使用脚本实现自动备份的方法及实战-猿码集

1. 背景介绍

SQL Server是一种关系型数据库管理系统(RDBMS)，用于存储和管理数据。对于企业级应用程序，数据备份是至关重要的。然而，手动执行数据备份花费时间长，还容易出错。因此，自动备份是一个很好的选择。本文将介绍如何使用脚本来自动备份SQL Server数据库，并实现自动备份的实战案例。

2. 自动备份的脚本实现

2.1 创建备份存储路径

首先，需要指定用于存储备份的路径。可以在SQL Server中创建一个文件夹，用于存储备份文件。在文件夹中，可以为每个数据库创建一个单独的子文件夹，以便备份文件被组织得更好。

--创建备份存储路径 EXEC master.dbo.xp_create_subdir 'C:\SQL_Backups' EXEC master.dbo.xp_create_subdir 'C:\SQL_Backups\example_database'

2.2 编写备份脚本

接下来，需要编写用于备份数据库的脚本。使用T-SQL命令可以自动生成脚本。备份脚本包括备份类型、备份文件路径、备份数据库的名称和备份的描述。

--完整备份脚本 BACKUP DATABASE [example_database] TO DISK = N'C:\SQL_Backups\example_database\example_database_full.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO --差异备份脚本 BACKUP DATABASE [example_database] TO DISK = N'C:\SQL_Backups\example_database\example_database_diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

在每个脚本中，都指定了备份文件的路径。需要注意的是，备份命令中的参数都控制着备份的进程。例如，DIFFERENTIAL修饰符将区别当前备份和上次完整备份之后更改的信息。NOINIT选项将保留现有备份，而不是覆盖上一个备份。

2.3 生成备份计划

由于需要自动备份数据库，因此应该安排一个计划，在指定的时间自动运行备份脚本。可以使用SQL Server代理来创建备份计划。


--设置备份计划
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'BackupExampleDatabase', 




@enabled=1, 




@notify_level_eventlog=0, 




@notify_level_email=2, 




@notify_level_netsend=0, 




@notify_level_page=0, 




@delete_level=0, 




@description=N'Backup Example Database to a local server every day at 2AM.', 




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




@owner_login_name=N'sa', @job_id = @jobId OUTPUT
SELECT @jobId
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'BackupExampleDatabaseSchedule', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20220712, 
        @active_end_date=99991231, 
        @active_start_time=20000, 
        @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:

在这个例子中，使用msdb系统数据库和存储过程创建了一个新的SQL Server代理，然后指定代理负责运行并维护这个计划。可以使用SQL Server Management Studio来设置其他代理属性，例如登录名和密码。

3. 自动备份的实战案例

接下来，我们将在Windows操作系统上实现SQL Server的自动备份。本实战案例将演示如何在Windows Server 2016中设置和运行SQL Server的自动备份。

3.1 安装SQL Server Express

在本例中，我们将使用SQL Server Express来演示自动备份。可以从Microsoft网站上下载和安装SQL Server的免费版本。请确保安装包含SQL Server代理，以便可以使用代理来管理备份计划。

3.2 创建备份存储路径并设置权限

为了存储备份文件，需要在计算机上创建一个目录并设置适当的权限。在SQL Server实例中创建一个文件夹，例如C:\SQL_Backups，在该文件夹中为每个数据库创建一个单独的子目录。确保Login帐户对C:\SQL_Backups以及它下面的所有文件夹和文件都具有读取和写入的权限。

3.3 创建备份脚本

使用前面提到的备份脚本，根据需要调整备份路径和是否进行完整和/或差异备份。将脚本保存在计算机上的任何位置。

3.4 配置备份计划

按照以下步骤设置SQL Server代理：

右键单击计算机上的SQL Server Management Studio，选择“以管理员身份运行”。

在左侧的“对象资源管理器”窗格中，展开“管理”文件夹，展开“SQL Server代理”文件夹，并单击“作业”文件夹。

右键单击“作业”文件夹并选择“新建作业”。

在“新建作业”对话框中，命名作业并输入作业说明。

点击“步骤”添加步骤。

在“新建步骤”对话框中，命名步骤，选择作业类型为“Transact-SQL脚本(T-SQL)”。

在“命令”文本框中输入要运行的备份脚本。

在“高级”选项卡中，可以指定作业的运行时间和频率。

点击“确定”。

现在，备份计划已经成功设置。SQL Server代理将按照指定的计划在每个时间间隔自动运行脚本。备份文件将被保存在指定的备份目录中。

4. 总结

自动备份是数据库管理中的一个重要方面，可以为企业级应用程序提供数据完整性和可靠性。本文介绍如何使用脚本实现SQL Server数据库的自动备份，并提供了一个实际案例演示在Windows Server 2016中如何设置并运行SQL Server的自动备份。实践证明，设置自动备份计划不仅节省了大量时间，同时也提高了数据安全性和数据的完整性。

SQL Server使用脚本实现自动备份的方法及实战