SQL Server 2005 如何複製資料庫

  分享到噗浪!

前幾天在開發專案的時候需要複製一個資料庫用來做測試,但之前每次都手動做備份、建立新資料庫、還原資料等動作還蠻費時的,因此心血來潮寫了一段「複製資料庫」的 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 資料庫中的資料直接覆蓋掉喔!!

此文章由 will 發表於 2008/4/25 下午 05:36:41

永久連結 | 評論 (15) | 此文章的RSSRSS comment feed |

分類: SQL Server

標籤: ,

評論

一月 16. 2009 07:25

Susan

請問以上語法可以將 預設值 , 與 PK , 都一起複製嗎 ?

Susan Taiwan

一月 16. 2009 17:19

will

當然可以。

will Taiwan

一月 16. 2009 17:31

Susan

事實上 , 我客戶端還是只用 SQL2000 , 最近作資料庫複製 , 也就是交易式與快照式的複製設定 , 卻發覺PK與預設值及自動增值的設定 , 居然不會一起複製過去 , 大概看了解說 , 好像還要寫一個 script 檔案去run , 去程式設計俱樂部搜尋還看不到解答 , 也還在研究中 , 不知您是否有其他意見提供我 , 感謝你 !

Susan Taiwan

一月 16. 2009 17:34

will

你可以上網找找看有沒有人已經寫好了

will Taiwan

二月 4. 2009 23:22

ARTHUR

請教:
1.您提及的"
       基本上只需要修改「參數設定區」的 @Source_DB 與 @Target_DB 即可成功複製資料庫。
        @Source_DB : 來源資料庫
        @Target_DB : 目的資料庫 ( 即新的資料庫 )    
                                                     "
  是指
       SET @Source_DB = 'MyDB';
      SET @Target_DB = 'MyDB_TEMP';
  這二行嗎?  還是所有「參數設定區」的 @Source_DB 與 @Target_DB

2您所說的"
      如果你原本的資料庫邏輯名稱不是預設的話,你要自己修改
           @Source_DB_LogicalName_Dat 與 @Source_DB_LogicalName_Log 參數。
  是什麼意思呢? 抱歉我並不了解?能否告知?

ARTHUR Taiwan

二月 5. 2009 05:25

will

1. Yes, 是指
SET @Source_DB = 'MyDB';
SET @Target_DB = 'MyDB_TEMP';

2. 這是指資料庫的"邏輯名稱",詳情請參閱 SQL Server 相關書籍。

will Taiwan

七月 24. 2009 22:03

sata

我可以請問一下
我來源和目的DB在不同IP耶
那我可以使用這個程式嗎?
該如何修改
而且是來源資料在SQL 2000
而目的資料是在SQL 2005
因為使用他的「複製資料庫」時出現:
「SUBSTRING函數長度參數無效」
但由於我是COPY整個資料庫,他沒指明是哪個SUBSTRING
我也不知道該怎麼處理
可以請達人幫一下嗎~~~

sata Taiwan

一月 21. 2010 23:28

霆哥

好用!
感恩!!

霆哥 Taiwan

三月 14. 2010 19:23

豹哥

好用謝謝

豹哥 Taiwan

十月 26. 2010 02:14

troy

前輩您好

想請教一下,我是sql server 2000,您的master.sys.master_files
,我已經改成2000的 master.dbo.sysaltfiles,但執行之後,最後會出現以下狀況:

邏輯檔案 'demo' 不屬於資料庫 'test' 中的一部份。請使用 RESTORE FILELISTONLY (指令) 列出邏輯檔案的名稱。

不知是否有空可為小弟解答?謝謝您

troy Taiwan

十月 26. 2010 03:55

Will 保哥

troy: 不好意思,我已經很少用 SQL 2000 了,手邊也沒有測試資料庫,抱歉無法幫你。

Will 保哥 Taiwan

十二月 28. 2011 23:54

bella

您好:
使用時會有以下的ERROR,
訊息 3234,層級 16,狀態 2,行 45
邏輯檔案 'gdb_zoo_core' 不屬於資料庫 'new_gdb_zoo_core' 的一部分。請使用 RESTORE FILELISTONLY 列出邏輯檔案名稱。
訊息 3013,層級 16,狀態 1,行 45
RESTORE DATABASE 正在異常結束。
我使用RESTORE FILELISTONLY也是ERROR,試了好久......

bella Taiwan

十二月 29. 2011 00:22

bella

我已解決了,謝謝!

bella Taiwan

二月 22. 2013 18:53

Eric

保哥您好!!

想請問你

假如我在同一台SERVER上

有兩個資料庫(使用SQL SERVER 2005)

我要將A資料庫之TABLE 移到 B資料庫之TABLE  (兩個資料庫TABLE都有資料)
我下
INSERT INTO A.TABLE( a001 ,a002 )
SELECT a001 , a002
FROM B.TABLE
這樣可以嗎

Eric Taiwan

二月 28. 2013 14:31

Will 保哥

Eric: 你自己試一次就知道了,如果資料表結構一樣的話,應該是沒問題的。

Will 保哥 Taiwan

新增評論


( 您輸入的Email不會顯示於網站上 )

  Country flag

biuquote
  • 評論
  • 線上預覽
Loading