在網路上找到的，真的是還滿方便的！
用SQL Server內建的複製資料庫太麻煩了
基本上，只要貼上下面的語法再改一下DB name就好了
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
