好同學的部落格

   1: -----------------------------------------------------------

   2: -- 基本上只需要設定 @Source_DB 和 @Target_DB 就可以了    --

   3: -----------------------------------------------------------

   4:  

   5: -- 宣告來源資料庫的名稱

   6: DECLARE @Source_DB nvarchar(256);

   7: -- 宣告來源資料庫的「資料邏輯名稱」

   8: DECLARE @Source_DB_LogicalName_Dat nvarchar(256);

   9: -- 宣告來源資料庫的「紀錄邏輯名稱」

  10: DECLARE @Source_DB_LogicalName_Log nvarchar(256);

  11:  

  12: -- 宣告目的地的資料庫的名稱

  13: DECLARE @Target_DB nvarchar(256);

  14:  

  15: -- 取得資料庫本機的 DATA 資料目錄

  16: DECLARE @data_path nvarchar(256);

  17: DECLARE @data_file_mdf nvarchar(256);

  18: DECLARE @data_file_ldf nvarchar(256);

  19: SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)

  20:                   FROM master.sys.master_files

  21:                   WHERE database_id = 1 AND file_id = 1);

  22:  

  23: -- 取得資料庫本機的 Backup 資料目錄

  24: DECLARE @backup_path nvarchar(256);

  25: DECLARE @backup_file nvarchar(256);

  26: 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\'

  27:                   FROM master.sys.master_files

  28:                   WHERE database_id = 1 AND file_id = 1);

  29: ----------------

  30: -- 參數設定區 --

  31: ----------------

  32: SET @Source_DB = 'AdventureWorks';

  33: SET @Source_DB_LogicalName_Dat = @Source_DB

  34: SET @Source_DB_LogicalName_Log = @Source_DB + '_log';

  35: SET @Target_DB = 'AdventureWorks_Buckup';

  36:  

  37: SET @backup_file = @backup_path + @Source_DB + '.bak';

  38:  

  39: SET @data_file_mdf = @data_path + @Target_DB + '.mdf';

  40: SET @data_file_ldf = @data_path + @Target_DB + '_log.ldf';

  41:  

  42: -- 先將 @Source_DB 資料庫備份至 Backup 資料夾

  43: BACKUP DATABASE @Source_DB 

  44:    TO DISK=@backup_file;

  45:  

  46: -- 最後將傳回的資料記錄檔還原到 @Target_DB 資料庫

  47: RESTORE DATABASE @Target_DB

  48:    FROM DISK=@backup_file 

  49:    WITH MOVE @Source_DB_LogicalName_Dat TO @data_file_mdf,

  50:    MOVE @Source_DB_LogicalName_Log TO @data_file_ldf;

  51: GO