备份所有数据库

use master;
go
create procedure bakdb (@f varchar(max))
as
begin
declare @file varchar(max)
set @file=@f

declare auth_cur cursor for select name from sysdatabases where name not in ('master','model','msdb','reportserver','reportservertempdb','tempdb')
open auth_cur;
declare @dbname varchar(max)
fetch next from auth_cur into @dbname
while(@@fetch_status=0)
begin
declare @db varchar(max),@sql varchar(max)
set @db='['+@dbname+']'
set @sql = ''
set @sql+='DECLARE @filename VARCHAR(500);'
set @sql+='DECLARE @date DATETIME;'
set @sql+='DECLARE @OLD_DATE DATETIME;'
set @sql+='SET @date=GETDATE();'
set @sql+='SET @OLD_DATE=GETDATE()-5;' --超过5天的备份即将被删除
set @sql+='SET @FILENAME ='''+@file+'\'+replace(@db,'/','_')+'-''+CAST(DATEPART(YYYY,@DATE) AS VARCHAR(10))+''-''+CAST(DATEPART(MM,@DATE) AS VARCHAR(10))+''-''+CAST(DATEPART(DD,@DATE) AS VARCHAR(10))+''.BAK'';'
set @sql+='BACKUP DATABASE '+@db+' TO DISK = @filename WITH COMPRESSION;'
set @sql+='EXECUTE master.dbo.xp_delete_file 0,N'''+@file+''',N''bak'',@OLD_DATE,1;'

exec (@sql)
fetch next from auth_cur into @dbname
end
close auth_cur;
deallocate auth_cur;
end
GO

转载于:https://www.cnblogs.com/huanglong1987/p/7077280.html