– 這個範例可以備份所有的使用者資料庫，中文的資料庫名稱也可以支援！
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
