CREATE procedure sp_backDB @dbname varchar(100),@path varchar(100)
as
--create by Allen 2004-11-01 in order to backup database and verify database
declare @bakname varchar(100)
--declare @dbname varchar(100)
declare @sql varchar(8000)
--declare @sql1 varchar(8000)
--declare @path varchar(100)
--set @path='c:\'
--set @dbname='northwind'
set @bakname=@dbname+cast(datepart(weekday,getdate())-1 as varchar(1))
--星期日到星期六對應備份檔案是bak0到bak6
set @sql='BACKUP DATABASE ['+@dbname+'] TO DISK=N'''+@path+''+@bakname+'.bak'' WITH INIT,NOUNLOAD,NOSKIP,STATS=10,NOFORMAT'
--print @sql
set @sql=@sql+' DECLARE @i INT
select @i = position from msdb..backupset where database_name='''+@dbname+'''and type!='''+'F'''+'
and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='''+@dbname+''')
RESTORE VERIFYONLY FROM DISK = N'''+@path+''+@bakname+'.bak'''+' WITH FILE = @i'
--print @sql
exec(@sql)
就備份到指定的目錄下, 星期天到星期六依次迴圈,(0---6)
