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),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

begin

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 +'完成';

fetch next from tb into @CurrentDataBaseName

end

close tb

deallocate tb

print '备份所有数据库完成'

end

else

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

begin

set @CurrentBackString='

? ? USE [master]

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

NOREWIND,NOUNLOAD,RETAINDAYS=6;';

print @CurrentBackString;

exec sp_executesql @CurrentBackString;

print '差异备份数据库'+@CurrentDataBaseName +'完成';

fetch next from tb into @CurrentDataBaseName

end

close tb

deallocate tb

print '差异备份所有数据库完成'

end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!