您好,UncleToo欢迎您!  为了更好的浏览本站,请使用高版本浏览器
RSS  Tag     设为首页 | 加入收藏
 您所在的位置:首页 > 数据库技术 > SQL Server

SQLSERVER备份脚本

作者:  来源:互联网  日期:2013-11-02 16:50:24
收藏  评论:( 0 )  阅读:1367

看网上的例子写的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
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, NOINIT,NAME='''+@CurrentDataBaseName+'-'+@day+'-diff'',DIFFERENTIAL,SKIP,
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




除非特别声明,本站所有PHP教程及其他教程/文章均为原创、翻译或网友投稿,版权均归UncleToo中文网所有, 转载请注明作者及出处。
原文网址:http://www.uncletoo.com/html/sqlserver/526.html
读完这篇文章后,你是否有所收获? 分享是一种生活的信念!
  • 0
  • 0
我来说两句
更多>>网友评论