【新手笔记】SQL Server数据库定时恢复的一种实现方法
最近要做一个测试服务器,这要求每天晚上都把测试数据库里的数据恢复为初始状态。打算通过对初始状态的数据库备份一个文件.bak文件,通过这个备份文件来进行定时恢复。
(PS:确实也可以通过SQL代码直接对数据库中的表数据进行操作来编写一个特定的恢复脚本,但是我懒得去梳理数据库表的关系(哪些数据能删,哪些不能删,哪些因为没有被引用可以先删,哪些因为被引用要后删,新增数据哪些因为被引用先增,哪些因为依赖别的数据所以后增……)—— 所以直接通过.bak文件来个简单粗暴的!)
直接上步骤:
第一步:创建备份文件
这里有两种创建备份文件的方法,一种是通过SQL Server Management Studio以可视化的方式进行备份。另一种是通过SQL语句进行备份,语句如下:
BACKUP DATABASE TestDATABASE TO DISK = 'C:\Backup\TestDB_Backup.bak'
第二步:查询生成的备份文件的LogicalName
SQL语句如下:
RESTORE FILELISTONLY FROM DISK='C:\Backup\TestDB_Backup.bak'
查询结果如下:
其中黑圈圈到的LogicalName列中的值之后有用。
第三步:编写通过.bak文件恢复数据库的SQL语句
SQL完整语句如下:
--获取数据库的独占访问权
ALTER DATABASE TestDATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
--通过.bak文件恢复数据库的SQL语句
USE master
RESTORE DATABASE TestDATABASE
FROM DISK = 'C:\Backup\TestDB_Backup.bak'
WITH REPLACE,
--注意:这里MOVE后面的两个值来自之前第二步查询的LogicalName,TO后面的值是需要恢复的数据库文件及日志文件
MOVE 'data1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDATABASE.mdf',
MOVE 'log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDATABASE_log.ldf'
GO
--上面部分代码已经通过.bak文件恢复了数据库,下面这句代码将数据库的独占访问权释放
ALTER DATABASE TestDATABASE SET ONLINE
第四步:为数据库设置定时任务
- 找到数据库的SQL Server代理,如图:
注:如果代理没有启动,则鼠标右键后选择启动 - 展开代理,右键作业选择新建作业,如图:
- 在新建作业窗口左侧选择“常规”,在常规视图中设置名称和说明,如图:、
- 在新建作业窗口左侧选择“步骤”,在步骤视图中点击新建,如图:
- 在新建作业步骤窗口左侧选择“常规”,在常规视图中填写步骤名称、数据库、命令,其中命令使用第三步中的代码自行根据情况修改。如图:
注:这里命令可以直接使用第三步的代码,也可以将第三步中的代码封装成一个存储过程(或函数),这里的命令采用执行存储过程(或函数)的代码也行。 - 在新建作业窗口左侧选择高级,在高级视图中注意成功、失败时要执行的操作。这里因为是恢复数据库操作,比较简单,只有一个步骤,所以这里就都选择转到下一步就行。(PS:这里如果做其他任务,情自行视情况而定)。点击确定就新建好了一个步骤。如图:
此时会自动回到新建作业窗口,此时你能在右侧视图中看到已经有了一个步骤了,如图:
- 在新建作业窗口左侧选择计划,在右侧新建——为作业设置计划,如图:
注意:选择重复执行,并按需求设置频率和持续时间,最后通过摘要说明来确认设置是否正确。
这样,每天凌晨5点就会执行任务了。 - 在新建作业窗口点击确认就可以完成作业的新建。
这样,以后每天晚上的5:00数据库就自动通过.bak文件进行恢复了。