sqlserver 自动备份所有数据库的SQL

160 阅读 0 评论 106 点赞

我是靠谱客的博主追寻唇彩，最近开发中收集的这篇文章主要介绍sqlserver 自动备份所有数据库的SQL，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

复制代码代码如下:

use master 
declare @DbName varchar(60) 
declare @BackSql varchar(1000) 
declare myCursor cursor for 
SELECT [name] FROM SYSDATABASES 
where [name] not in ('master','model','msdb','tempdb') 
order by [name] 
open myCursor 
fetch next from myCursor into @DbName 
while(@@FETCH_STATUS = 0) 
begin 
if datename(weekday, getdate())='星期三' --每周三覆盖上周三的 
begin 
select @BackSql='Backup DATABASE ['+@DbName+'] to disk=''E:\DbBackUp\'+@DbName+'星期三.bak'' with format' 
end 
else--每天覆盖上一天的 
begin 
select @BackSql='Backup DATABASE ['+@DbName+'] to disk=''E:\DbBackUp\'+@DbName+'AutoBack.bak'' with format' 
end 
exec(@BackSql) 
fetch next from myCursor into @DbName 
end 
close myCursor 
DEALLOCATE myCursor 