sqlserver 完整备份、差异备份、日志备份与恢复脚本

手动备份请参考：http://wenku.baidu.com/view/d57f283f453610661ed9f46a.html?from=search

 

准备：

1.完整备份数据库

BACKUP DATABASE CollectionDB TO DISK = 'E:\20150609_75\bak\Test_bak_full.bak' WITH INIT

 

2.差异备份数据库

BACKUP DATABASE CollectionDB TO DISK = 'E:\20150609_75\bak\Test_bak_diff.bak' WITH INIT, DIFFERENTIAL   --加上DIFFERENTIAL代表差异备份

 

3.备份日志文件

BACKUP LOG CollectionDB TO DISK='c:\db_log.bak' WITH INIT

 

3.备份事务日志，文件名中包含当前时间，适合定时备份

DECLARE @strbackup NVARCHAR(100)

 --改为日期加时间的

 SET @strbackup = 'E:\20150609_75\bak\Test_bak_log_'

     + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', ''), ':', '') + '.bak'

 BACKUP LOG Test_Bak TO DISK = @strbackup WITH INIT;

 GO

 

 

一、还原完整+差异备份

1.备份完整备份文件

USE master; 

RESTORE DATABASE CollectionDB 

FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_07_31_040002_0771951.bak' 

WITH STATS = 10, REPLACE,NORECOVERY

GO

 

2.备份差异备份文件

USE master; 

RESTORE DATABASE CollectionDB 

FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_08_04_040001_5320399.bak' 

WITH STATS = 10, RECOVERY

GO

 

二、还原完整备份+差异备份+日志文件

1.还原完整备份文件

USE master; 

RESTORE DATABASE CollectionDB 

FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_07_31_040002_0771951.bak' 

WITH STATS = 10, REPLACE,NORECOVERY

GO

 

2.还原差异备份文件

（只有一个差异备份的情况，当还原多个差异备份时，通常前面的都用WITH NORECOVERY，还原最后一个差异备份才能用WITH RECOVERY）

USE master; 

RESTORE DATABASE CollectionDB 

FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_08_04_040001_5320399.bak' 

WITH STATS = 10, NORECOVERY

GO

 

3.还原日志备份

RESTORE LOG CollectionDB 

FROM DISK = N'C:\db_log.bak' 

WITH STATS = 10, RECOVERY ,STOPAT='2016-08-04 14:30:00'

GO

 

三、还原完整备份+日志文件

1.还原完整备份文件

USE master; 

RESTORE DATABASE CollectionDB 

FROM DISK='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\Backup\CollectionDB_backup_2016_07_31_040002_0771951.bak' 

WITH STATS = 10, REPLACE,NORECOVERY

GO

 

2.还原日志备份

RESTORE LOG CollectionDB 

FROM DISK = N'C:\db_log.bak' 

WITH STATS = 10, RECOVERY ,STOPAT='2016-08-04 14:30:00'

GO

 

 

参数说明：

WITH MOVE TO：重新指定文件的路径，WITH MOVE TO数量取决于数据库文件数量

STATS = 10：没完成10%显示一条记录

REPLACE：覆盖现有数据库

NORECOVERY：不对数据库进行任何操作，不回滚未提交的事务