The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

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 資料庫中的資料直接覆蓋掉喔!!

評論 (15) -

  • Susan

    2009/1/16 上午 07:25:59 |

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

  • will

    2009/1/16 下午 05:19:55 |

    當然可以。

  • Susan

    2009/1/16 下午 05:31:39 |

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

  • will

    2009/1/16 下午 05:34:50 |

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

  • ARTHUR

    2009/2/4 下午 11:22:31 |

    請教:
    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 參數。
      是什麼意思呢? 抱歉我並不了解?能否告知?

  • will

    2009/2/5 上午 05:25:33 |

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

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

  • sata

    2009/7/24 下午 10:03:05 |

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

  • 霆哥

    2010/1/21 下午 11:28:47 |

    好用!
    感恩!!

  • 豹哥

    2010/3/14 下午 07:23:32 |

    好用謝謝

  • troy

    2010/10/26 上午 02:14:36 |

    前輩您好

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

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

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

  • Will 保哥

    2010/10/26 上午 03:55:53 |

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

  • bella

    2011/12/28 下午 11:54:49 |

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

  • bella

    2011/12/29 上午 12:22:54 |

    我已解決了,謝謝!

  • Eric

    2013/2/22 下午 06:53:21 |

    保哥您好!!

    想請問你

    假如我在同一台SERVER上

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

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

  • Will 保哥

    2013/2/28 下午 02:31:27 |

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

blog comments powered by Disqus