前幾天在開發專案的時候需要複製一個資料庫用來做測試,但之前每次都手動做備份、建立新資料庫、還原資料等動作還蠻費時的,因此心血來潮寫了一段「複製資料庫」的 T-SQL 幫我執行複製資料庫的工作,自己覺得還含蠻好用的,T-SQL 程式如下:
-- 宣告來源資料庫的名稱
DECLARE @Source_DB nvarchar(256);
-- 宣告來源資料庫的「資料邏輯名稱」
DECLARE @Source_DB_LogicalName_Dat nvarchar(256);
-- 宣告來源資料庫的「紀錄邏輯名稱」
DECLARE @Source_DB_LogicalName_Log nvarchar(256);
-- 宣告目的地的資料庫的名稱
DECLARE @Target_DB nvarchar(256);
-- 取得資料庫本機的 DATA 資料目錄
DECLARE @data_path nvarchar(256);
DECLARE @data_file_mdf nvarchar(256);
DECLARE @data_file_ldf nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- 取得資料庫本機的 Backup 資料目錄
DECLARE @backup_path nvarchar(256);
DECLARE @backup_file nvarchar(256);
SET @backup_path = (SELECT SUBSTRING(SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1), 1, CHARINDEX(N'\DATA\', LOWER(physical_name)) - 1) + '\Backup\'
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
----------------
-- 參數設定區 --
----------------
SET @Source_DB = 'MyDB';
SET @Source_DB_LogicalName_Dat = @Source_DB
SET @Source_DB_LogicalName_Log = @Source_DB + '_log';
SET @Target_DB = 'MyDB_TEMP';
SET @backup_file = @backup_path + @Source_DB + '.bak';
SET @data_file_mdf = @data_path + @Target_DB + '.mdf';
SET @data_file_ldf = @data_path + @Target_DB + '_log.ldf';
-- 先將 @Source_DB 資料庫備份至 Backup 資料夾
BACKUP DATABASE @Source_DB
TO DISK=@backup_file;
-- 最後將傳回的資料記錄檔還原到 @Target_DB 資料庫
RESTORE DATABASE @Target_DB
FROM DISK=@backup_file
WITH MOVE @Source_DB_LogicalName_Dat TO @data_file_mdf,
MOVE @Source_DB_LogicalName_Log TO @data_file_ldf;
GO
使用說明:
- 基本上只需要修改「參數設定區」的 @Source_DB 與 @Target_DB 即可成功複製資料庫。
@Source_DB : 來源資料庫
@Target_DB : 目的資料庫 ( 即新的資料庫 )
- 如果你原本的資料庫邏輯名稱不是預設的話,你要自己修改 @Source_DB_LogicalName_Dat 與 @Source_DB_LogicalName_Log 參數。
- 如果你的資料庫中已經有 @Target_DB 所定義的這個資料庫名稱的話,所有資料都會被 @Source_DB 資料庫中的資料直接覆蓋掉喔!!