合 SQL Server生成全量+差异备份及还原脚本
Tags： MSSQLSQL Server备份恢复脚本全量增量差异还原
👉 本文共约265个字，系统预计阅读时间或需1分钟。
有的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; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
