杨叔叔 发表于 2018-10-15 07:06:48

Sql Server 生动备份脚本

　　USE
　　GO
　　SET ANSI_NULLS ON
　　GO
　　SET QUOTED_IDENTIFIER ON
　　GO
　　DECLARE @FullPath varchar(1000),@FolderPath varchar(100),@FileName varchar(20)
　　set @FolderPath='F:\Sery\'
　　set @FullPath = @FolderPath+ 'testg4_'+convert(VARCHAR(4),year(getdate()))+right( '0'+convert(VARCHAR(2),MONTH(getdate())),2)+right('0'+convert(VARCHAR(2),DAY(getdate())),2)+ '.bak'
　　BACKUP DATABASE TODISK =@FullPath WITHNOINIT ,NOUNLOAD ,NOSKIP ,STATS = 10,NOFORMAT
　　Sql Recurrent Backup
　　USE
　　GO
　　SET ANSI_NULLS ON
　　GO
　　SET QUOTED_IDENTIFIER ON
　　GO
　　DECLARE @FullPath varchar(1000),@FolderPath varchar(100),@FileName varchar(20)
　　set @FolderPath='D:\Sery\Sql Manual Backup\'
　　set @FullPath = @FolderPath+ 'g4pro_'+datename(weekday,getdate())+ '.bak'
　　BACKUP DATABASE TODISK =@FullPath WITHINIT ,NOUNLOAD , NOSKIP ,STATS = 10,NOFORMAT
　　remark
　　datebase:testg4
　　This is backup policy script of sql server created
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\master'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\model'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\msdb'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\ReportServer'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\ReportServerTempDB'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\KDAcctDB'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20120726094221'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20120726094613'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20120726095348'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20120726095811'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20120726101043'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20120726101227'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\AIS20101126160333'
　　GO
　　EXECUTE master.dbo.xp_create_subdir N'D:\Kingdee\k3backup\jbcus'
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\master\master_backup_2012_08_03_082319_2260499.bak' WITH NOFORMAT, NOINIT,NAME = N'master_backup_2012_08_03_082319_2104475', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'master' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'master' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“master”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\master\master_backup_2012_08_03_082319_2260499.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\model\model_backup_2012_08_03_082319_2260499.bak' WITH NOFORMAT, NOINIT,NAME = N'model_backup_2012_08_03_082319_2260499', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'model' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'model' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“model”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\model\model_backup_2012_08_03_082319_2260499.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\msdb\msdb_backup_2012_08_03_082319_2260499.bak' WITH NOFORMAT, NOINIT,NAME = N'msdb_backup_2012_08_03_082319_2260499', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'msdb' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'msdb' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“msdb”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\msdb\msdb_backup_2012_08_03_082319_2260499.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\ReportServer\ReportServer_backup_2012_08_03_082319_2416523.bak' WITH NOFORMAT, NOINIT,NAME = N'ReportServer_backup_2012_08_03_082319_2416523', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'ReportServer' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ReportServer' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“ReportServer”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\ReportServer\ReportServer_backup_2012_08_03_082319_2416523.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\ReportServerTempDB\ReportServerTempDB_backup_2012_08_03_082319_2416523.bak' WITH NOFORMAT, NOINIT,NAME = N'ReportServerTempDB_backup_2012_08_03_082319_2416523', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'ReportServerTempDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ReportServerTempDB' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“ReportServerTempDB”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\ReportServerTempDB\ReportServerTempDB_backup_2012_08_03_082319_2416523.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\KDAcctDB\KDAcctDB_backup_2012_08_03_082319_2416523.bak' WITH NOFORMAT, NOINIT,NAME = N'KDAcctDB_backup_2012_08_03_082319_2416523', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'KDAcctDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'KDAcctDB' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“KDAcctDB”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\KDAcctDB\KDAcctDB_backup_2012_08_03_082319_2416523.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20120726094221\AIS20120726094221_backup_2012_08_03_082319_2416523.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20120726094221_backup_2012_08_03_082319_2416523', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20120726094221' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20120726094221' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20120726094221”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20120726094221\AIS20120726094221_backup_2012_08_03_082319_2416523.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20120726094613\AIS20120726094613_backup_2012_08_03_082319_2572547.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20120726094613_backup_2012_08_03_082319_2572547', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20120726094613' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20120726094613' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20120726094613”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20120726094613\AIS20120726094613_backup_2012_08_03_082319_2572547.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20120726095348\AIS20120726095348_backup_2012_08_03_082319_2572547.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20120726095348_backup_2012_08_03_082319_2572547', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20120726095348' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20120726095348' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20120726095348”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20120726095348\AIS20120726095348_backup_2012_08_03_082319_2572547.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20120726095811\AIS20120726095811_backup_2012_08_03_082319_2572547.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20120726095811_backup_2012_08_03_082319_2572547', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20120726095811' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20120726095811' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20120726095811”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20120726095811\AIS20120726095811_backup_2012_08_03_082319_2572547.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20120726101043\AIS20120726101043_backup_2012_08_03_082319_2572547.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20120726101043_backup_2012_08_03_082319_2572547', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20120726101043' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20120726101043' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20120726101043”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20120726101043\AIS20120726101043_backup_2012_08_03_082319_2572547.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20120726101227\AIS20120726101227_backup_2012_08_03_082319_2728571.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20120726101227_backup_2012_08_03_082319_2728571', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20120726101227' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20120726101227' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20120726101227”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20120726101227\AIS20120726101227_backup_2012_08_03_082319_2728571.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\AIS20101126160333\AIS20101126160333_backup_2012_08_03_082319_2728571.bak' WITH NOFORMAT, NOINIT,NAME = N'AIS20101126160333_backup_2012_08_03_082319_2728571', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'AIS20101126160333' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AIS20101126160333' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“AIS20101126160333”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\AIS20101126160333\AIS20101126160333_backup_2012_08_03_082319_2728571.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND
　　GO
　　BACKUP DATABASE TODISK = N'D:\Kingdee\k3backup\jbcus\jbcus_backup_2012_08_03_082319_2728571.bak' WITH NOFORMAT, NOINIT,NAME = N'jbcus_backup_2012_08_03_082319_2728571', SKIP, REWIND, NOUNLOAD,STATS = 10
　　GO
　　declare @backupSetId as int
　　select @backupSetId = position from msdb..backupset where database_name=N'jbcus' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'jbcus' )
　　if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“jbcus”的备份信息。', 16, 1) end
　　RESTORE VERIFYONLY FROMDISK = N'D:\Kingdee\k3backup\jbcus\jbcus_backup_2012_08_03_082319_2728571.bak' WITHFILE = @backupSetId,NOUNLOAD,NOREWIND

页: [1]
查看完整版本: Sql Server 生动备份脚本