备份任意一个instance中所有的数据库

Editor
Apr 30, 2014 at 9:04 AM
USE [master]
GO

/ Object: StoredProcedure [dbo].[do_full_instanse_backup] Script Date: 2014/1/24 16:31:34 /
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




/备份instant上说有db/
create proc [dbo].[do_full_instanse_backup]
@backup_trace nvarchar(255)='D:\MSSQL\Backup\'
as
declare cur1 cursor for SELECT Name FROM Master.sys.SysDatabases where dbid>4
declare @i int
declare @num int
declare @dbname nvarchar(255)
declare @sqlstr nvarchar(500)
declare @sqlstr2 nvarchar(500)
select @num=count(*) from Master.sys.SysDatabases where dbid>4
set @i=1
open cur1
while @i<=@num
begin
fetch cur1 into @dbname
set @sqlstr=@backup_trace+@dbname+'_'+replace(replace(replace(convert(nvarchar(255),getdate(),120),'-',''),' ',''),':','_')+'.bak'
set @sqlstr2='N'''+@dbname+'-Full Database Backup'
BACKUP DATABASE @dbname TO  DISK = @sqlstr WITH FORMAT, INIT,  MEDIANAME = @dbname , NAME =@sqlstr2, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10  
set @i=@i+1
end
close cur1
deallocate cur1



GO
Marked as answer by network_sicong on 4/30/2014 at 1:04 AM