SQL Server生成全量+差异备份及还原脚本
Tags： MSSQLSQL Server全量增量备份恢复差异脚本还原
👉 本文共约267个字，系统预计阅读时间或需2分钟。
有的SQL Server实例的数据库比较多，若全部进行备份，使用图形化界面比较慢，还容易出错，所以，建议使用命令来备份，可以使用如下的命令来生成备份和还原的命令：
SQL Server2008R2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | -- 生成全备备份脚本 SELECT 'BACKUP DATABASE ['+name+'] TO DISK = N''D:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'.bak'' with format,stats=5,compression;' as 'bk_scripts' FROM sys.databases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') and state=0 ; -- 生成差异备份脚本 SELECT 'BACKUP DATABASE ['+name+'] TO DISK = N''D:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'_inc.bak'' with Differential,stats=5,compression;' as 'bk_scripts' FROM sys.databases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') and state=0 ; -- 生成全量还原脚本 SELECT 'RESTORE DATABASE ['+name+'] FROM DISK = N''D:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'.bak'' with stats=5;' as 'restore_scripts' FROM sys.databases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') and state=0 ; -- 生成差异备份还原脚本 SELECT 'RESTORE DATABASE ['+name+'] FROM DISK = N''D:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'.bak'' with NORECOVERY,stats=5;' as 'restore_scripts' FROM sys.databases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') and state=0 ; -- 结束还原 SELECT 'RESTORE DATABASE ['+name+'] with recovery;' as 'restore_scripts' FROM sys.databases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') order by name; restore database [tpcc] with recovery; -- 全量备份 BACKUP DATABASE [lhrdb] TO DISK = N'D:\backup\lhrdb_20210916.bak' with stats=10; GO BACKUP LOG [lhrdb] TO DISK = N'D:\backup\lhrdb_20210916_LOG.bak' with stats=10; GO -- 全量恢复 RESTORE DATABASE [lhrdb] FROM DISK=N'D:\backup\lhrdb_20210916.bak' with stats=10; GO -- 差异备份 Backup Database [lhrdb] TO DISK = N'D:\backup\lhrdb_20210916_inc1.bak' with Differential; |
SQL Server 2000
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- MSSQL 2000生成全备备份脚本 SELECT 'BACKUP DATABASE ['+name+'] TO DISK = N''E:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'.bak'' with format,stats=5;' as 'bk_scripts' FROM master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') order by name; -- 生成差异备份脚本 SELECT 'BACKUP DATABASE ['+name+'] TO DISK = N''E:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'_inc.bak'' with Differential,stats=5;' as 'bk_scripts' FROM master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') order by name; -- 生成全量或增量还原脚本 SELECT 'RESTORE DATABASE ['+name+'] FROM DISK = N''F:\backup\'+name+'_'+CONVERT(varchar(11),GETDATE(),112)+'.bak'' with NORECOVERY,stats=5;' as 'restore_scripts' FROM master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') order by name; -- 结束还原 SELECT 'RESTORE DATABASE ['+name+'] with recovery;' as 'restore_scripts' FROM master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') order by name; |
注意
1、使用SSMS或SQL（backup database）进行备份，备份后的备份文件默认都放在服务器端，所以需要保证服务器端有相关的备份目录。
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
2、若本地空间不足，那么也可以备份到共享文件夹，不过效率真的很低。
备份文件到共享目录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- 如果xp_cmdshell没有启用，请先启用 sp_configure 'show advanced options',1 RECONFIGURE GO sp_configure 'xp_cmdshell',1 RECONFIGURE GO 目标共享目录：\\10.10.56.43\AutoBackup 共享目录账号密码:administrator\123456 -- 1.先创建映射--EXEC MASTER.dbo.xp_cmdshell 'net use \\服务器IP\共享文件夹 用户密码 /user:服务器IP\登录用户名' EXEC MASTER.dbo.xp_cmdshell 'net use \\10.10.56.43\AutoBackup "123456" /user:10.10.56.43\Administrator' -- 2.利用映射备份数据库 BACKUP DATABASE affair_stydy to disk='\\10.10.56.43\AutoBackup\affair_stydy.bak' --全备份 BACKUP DATABASE TestBackup] TO DISK = N'D:\database backup\TestBackupDB-diff1.bak' WITH DIFFERENTIAL --差异备份 -- 3.删除映射 EXEC MASTER.dbo.xp_cmdshell 'net use \\10.10.56.43\AutoBackup /delete' |
