摘要:MSSQL資料庫全備份
MSSQL 資料庫全備份
-- 這個範例可以備份所有的使用者資料庫，中文的資料庫名稱也可以支援！
declare @DatabaseName nvarchar(300) -- 存放資料庫名稱
,@BackupSQL nvarchar(4000) -- 存放備份的T-SQL
,@Timestamp varchar(30) -- 存放時間標記
,@DirectoryPath nvarchar(2000) -- 存放備份檔放置的資料夾的路徑
,@FullPath nvarchar(2500) -- 存放備份檔放置的完整路徑
,@RecoveryModel int -- 存放還原模式
-- 指定備份檔放置的資料夾的路徑
set @DirectoryPath = 'C:\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