SQL備份所有資料庫腳本

--SQL備份所有資料庫腳本

declare @CurrentDataBaseName nvarchar(100)

declare @CurrentBackFolder nvarchar(200)

declare @CurrentBackString nvarchar(2000)

set @CurrentBackFolder='D:\Test'--這裡是備份的目錄,所有資料庫都備份到這個目錄

--查詢所有資料庫名稱

--select * from master..sysdatabases where dbid>=7

declare tb cursor local for select name,dbid 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 ['[email protected]+'] TO DISK = '''+ @CurrentBackFolder+'\'[email protected]+convert(varchar(50),getdate(),112)+'.bak'' WITH NOFORMAT, NOINIT,NAME ='''[email protected]+'-完整 資料庫 備份'',SKIP, NOREWIND, NOUNLOAD;';

print @CurrentBackString;

exec sp_executesql @CurrentBackString;

print '備份資料庫'[email protected] +'完成';

fetch next from tb into @CurrentDataBaseName

end

close tb

deallocate tb

print '備份所有資料庫完成'