SQLSERVER備份腳本

看網上的例子寫的sqlserver資料庫備份腳本，這個腳本不是很好，不能删除以前備份的。

declare @CurrentDataBaseName nvarchar(100)

declare @CurrentBackFolder nvarchar(200)

declare @WeekDay VARCHAR(20)

declare @CurrentBackString nvarchar(2000)

declare @day VARCHAR(20)

--SELECT GETDATE() AS 'Current Date'

set @day=convert(varchar(100),getdate(),112)

set @WeekDay = DATEPART(WEEKDAY, GETDATE())

set @CurrentBackFolder='D:\Test'

--set @CurrentDataBaseName='ceshi'

--+convert(varchar(50),getdate(),112),dbid

--select * from   master..sysdatabases   where   dbid>=7

--weekday 1 表示星期日

if @WeekDay = '1'

begin

declare tb cursor local for select name from master..sysdatabases where   dbid>=7;

open tb

fetch next from tb into @CurrentDataBaseName

while @@fetch_status=0

set @CurrentBackString='

    USE [master]

BACKUP DATABASE ['+@CurrentDataBaseName+']  TO DISK = '''+@CurrentBackFolder+'\'+@CurrentDataBaseName+'.bak'' WITH NOFORMAT, NOINIT,NAME='''+@CurrentDataBaseName+'-'+@day+''',SKIP, NOREWIND,NOUNLOAD;';

print @CurrentBackString;

exec sp_executesql @CurrentBackString;

print '備份資料庫'+@CurrentDataBaseName +'完成';

end

close tb

deallocate tb

print '備份所有資料庫完成'

else

BACKUP DATABASE ['+@CurrentDataBaseName+']  TO DISK = '''+@CurrentBackFolder+'\'+@CurrentDataBaseName+'.bak'' WITH NOFORMAT, NOINIT,NAME='''+@CurrentDataBaseName+'-'+@day+'-diff'',DIFFERENTIAL,SKIP,

NOREWIND, NOUNLOAD,RETAINDAYS=6;';

print '差異備份資料庫'+@CurrentDataBaseName +'完成';

print '差異備份所有資料庫完成'

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1207948，如需轉載請自行聯系原作者