Blueimp 論壇首頁
  首頁  | 討論區  | 最新話題  | 搜尋  | XML  |  登入
博客來購書 | 《主管這樣帶人就對了!》
貝殼鯨魚兒童程式啟蒙教育課程(點我去報名)

此話題中所有文章數: 2 [ 話題狀態: 一般 ]
上一話題 此文章已經觀看 13075 次 而且有 篇回應 下一話題
會員大頭照
男寶寶 jieh
《騎士團團長》
文章: 6699

MS SQL 資料庫備份語法(database mssql backup script)



關鍵語法就是 backup database [@db_name] to disk = '@path_filename'
而以下範例我將檔明訂為 資料庫名稱 + 時間戳記 (yyyymmddhhmuss),檔名原則就看自己怎麼湊囉~~

一、簡單的範例(mssql backup script example):單一資料庫備份

declare @TSQL varchar(4000), @db_name varchar(50), @DirectoryPath varchar(50), @Timestamp varchar(20)

set @DirectoryPath = 'D:\MSSQL\Backup\'
set @db_name = 'DASP-SIT'
set @Timestamp = convert(char(8), getdate(), 112) + replace(convert(varchar, getdate(), 108), ':', '')

set @TSQL = 'backup database [' + @db_name + '] to disk = '''
+ @DirectoryPath + @db_name + '_' + @Timestamp
+ '.bak'' WITH NOFORMAT, NOINIT, SKIP'

exec (@TSQL)

延伸閱讀文章:用 SQL 語法動態組合 SQL

二、多資料庫&管理
只要搭配 cursor fetch 和建立 table 來控制備份的時效,就能靠 JOB 用純 SQL 設計出一套資料庫備份的自動管理機制。

最下面附上實際案例原始碼(Full MSSQL database backup script)。

(轉貼時請加註本行)
引用自:藍色小惡魔MS SQL 資料庫備份語法
http://www.imp.idv.tw/play/forum/viewthread?thread=2908

/*
====================================================
2009-09-12 : Create by Jieh
Description : 進行資料庫檔案備份 (壓縮)
====================================================
*/
-- step 1 backup database
declare @TSQL varchar(4000), @db_name varchar(50), @DirectoryPath varchar(50), @Timestamp varchar(20)
set @DirectoryPath = 'G:\DBBackup\'
set @Timestamp = convert(char(8), getdate(), 112) + replace(convert(varchar, getdate(), 108), ':', '')

-- select * from sysdatabases -- SQL2000 : sysdatabases, SQL2005 : sys.databases
declare Database_Cursor cursor for
select name from sysdatabases
join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

open Database_Cursor fetch next from Database_Cursor into @db_name
while @@fetch_status = 0 begin -- print @db_name
set @TSQL = 'backup database ' + @db_name + ' to disk = '''
+ @DirectoryPath + @db_name + '_' + @Timestamp
+ '.bak'' WITH NOFORMAT, NOINIT, SKIP'
exec (@TSQL)
fetch next from Database_Cursor into @db_name
end
close Database_Cursor deallocate Database_Cursor

-- step 1.1 successful log
declare @db_name varchar(50), @log varchar(1000)
set @log = '今日完成資料庫備份::'

declare Database_Cursor cursor for
select name from sysdatabases
join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

open Database_Cursor fetch next from Database_Cursor into @db_name
while @@fetch_status = 0 begin
set @log = @log + ' > ' + @db_name
fetch next from Database_Cursor into @db_name
end
close Database_Cursor deallocate Database_Cursor
-- print @log
UPDATE [MIS_PROCESS].MIS.DBO.MIS_PROCESS_LIST SET
MPL_FINAL_EXEC_DATE = convert(char(10), getdate(), 120),
MPL_FINAL_EXEC_TIME = convert(char(8), getdate(), 108),
MPL_FINAL_EXEC_RESULT = 'SUCCESSFUL',
MPL_REMARK = @log
WHERE MPL_CODE = 'DBAM1101'

-- step 1.2 fail log
UPDATE [MIS_PROCESS].MIS.DBO.MIS_PROCESS_LIST SET
MPL_FINAL_EXEC_DATE = convert(char(10), getdate(), 120),
MPL_FINAL_EXEC_TIME = convert(char(8), getdate(), 108),
MPL_FINAL_EXEC_RESULT = 'FAIL',
MPL_REMARK = '資料庫備份失敗!'
WHERE MPL_CODE = 'DBAM1101'

-- step 1.3 MIS_PROCESS_HIRSTORY log
insert into [MIS_PROCESS].MIS.DBO.MIS_PROCESS_HISTORY
(MPH_MPLCODE,MPH_EXEC_DATE,MPH_EXEC_TIME,MPH_RESULT,MPH_REMARK)

select MPL_CODE,MPL_FINAL_EXEC_DATE,MPL_FINAL_EXEC_TIME,MPL_FINAL_EXEC_RESULT,MPL_REMARK
from [MIS_PROCESS].MIS.DBO.MIS_PROCESS_LIST where MPL_CODE = 'DBAM1101'


-- step 2 shink database file
declare @TSQL varchar(4000), @db_name varchar(50), @shrink_name varchar(50)

declare db_cursor cursor for
select name from sysdatabases
join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

declare shrink_cursor cursor for
select DB_SHRINKFILE_NAME from sysdatabases
join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

open db_cursor fetch next from db_cursor into @db_name
open shrink_cursor fetch next from shrink_cursor into @shrink_name
while @@fetch_status = 0 begin -- print @db_name
exec ('alter database ' + @db_name + ' set AUTO_SHRINK OFF')
set @TSQL = 'use [' + @db_name + '] dbcc shrinkfile (N''' + @shrink_name + ''')'
exec (@TSQL)
fetch next from db_cursor into @db_name
fetch next from shrink_cursor into @shrink_name
end
close db_cursor deallocate db_cursor
close shrink_cursor deallocate shrink_cursor

----------------------------------------
支持小惡魔
BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx
LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA
知識跟八卦一樣,越多人知道越有價值;知識最好的備份方法,散播!
藍色小惡魔(林永傑): 臉書
----------------------------------------
[編輯文章 16 次, 最後修改: jieh 於 2016/12/13 上午 11:38:36]

[2011/9/16 下午 04:49:49] 友善列印   [返迴此篇文章頂端 ]  回到頂端 
會員大頭照
男寶寶 jieh
《騎士團團長》
文章: 6699

Re: MS SQL 資料庫備份語法

http://blueimp.pixnet.net/blog/post/36544753

2016/7/27:6225+356
----------------------------------------
支持小惡魔
BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx
LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA
知識跟八卦一樣,越多人知道越有價值;知識最好的備份方法,散播!
藍色小惡魔(林永傑): 臉書
----------------------------------------
[編輯文章 3 次, 最後修改: jieh 於 2016/10/13 下午 06:58:51]
[2016/7/27 下午 05:42:41] 友善列印   [返迴此篇文章頂端 ]  回到頂端 
[顯示可以列印的版本]