如何使用 T-SQL 來備份所有的使用者資料庫

-- 這個範例可以備份所有的使用者資料庫,中文的資料庫名稱也可以支援!

declare @DatabaseName nvarchar(300) -- 存放資料庫名稱

,@BackupSQL nvarchar(4000) -- 存放備份的T-SQL

,@Timestamp varchar(30) -- 存放時間標記

,@DirectoryPath nvarchar(2000) -- 存放備份檔放置的資料夾的路徑

,@FullPath nvarchar(2500) -- 存放備份檔放置的完整路徑

,@RecoveryModel int -- 存放還原模式

-- 指定備份檔放置的資料夾的路徑

set @DirectoryPath = 'D:\Backup\'

-- create a timestamp for the backup file name

set @Timestamp = convert(varchar, getdate(),112) +

replace(convert(varchar, getdate(),108), ':', '')

-- get user database only

declare Database_Cursor cursor for

select d.name

from sys.databases d

where d.name not in('master', 'tempdb', 'model', 'msdb')

open Database_Cursor

fetch next from Database_Cursor

into @DatabaseName

while @@fetch_status = 0

begin

-- backup database

set @FullPath = ''

set @FullPath = @DirectoryPath + @DatabaseName

exec sys.xp_create_subdir @FullPath

set @BackupSQL = ''

set @BackupSQL = @BackupSQL + 'BACKUP DATABASE ' +

@DatabaseName + ' TO DISK = N''' + @FullPath + + '\' +

@DatabaseName + '_' + @Timestamp + '.bak''

WITH NOFORMAT, NOINIT, SKIP'

-- 執行資料庫備份

exec (@BackupSQL)

-- backup transaction log

select @RecoveryModel = d.recovery_model from sys.databases

as d where d.name = @DatabaseName

-- only backup transaction logs for databases set for full recovery

if @RecoveryModel = 1 -- recovery model = full

begin

set @BackupSQL = N''

set @BackupSQL = @BackupSQL + 'BACKUP LOG ' +

@DatabaseName + ' TO DISK = N''' + @FullPath + + '\' +

@DatabaseName + '_' + @Timestamp + '.trn''

WITH NOFORMAT, NOINIT, SKIP'

-- 執行交易記錄檔備份

exec (@BackupSQL)

end

fetch next from Database_Cursor

into @DatabaseName

end

-- 釋放資源

close Database_Cursor

deallocate Database_Cursor