SQL資料庫完全複製

標籤：style   使用   os   strong   檔案   資料   for   工作   

很少摸 Windows 環境下的東西，最近被個 MS SQL Server 的資料庫搞得頭大。實在不像 MySQL 那樣用起來輕車熟路， OrZ ...

本來以為企業管理器裡面既然提供了 DTS 資料匯入/匯出 的功能，直接使用 DTS 指定源和目標庫就可以完全複製資料庫到遠程伺服器上了。結果發現除了表的基本結構和資料，欄位約束、索引值、預設值等都沒過去。最終還是要靠直接磁碟拷貝資料檔案了事。

如果資料庫服務可以停下來的話，最快的方法是直接拷貝對應的 .mdf 和 .ldf 檔案（在企業管理器選擇資料庫右鍵看屬性的“資料檔案”項確定檔案名稱）；然後執行T-SQL命令（比如新的目標資料庫名為 test ）：

-- 如果 test 資料庫已經存在，需要先把其分離。
-- 如果已經有應用串連到該資料庫的話，使用以下語句強制斷開現有串連
ALTER DATABASE test

 
      SET SINGLE_USER 
      WITH ROLLBACK IMMEDIATE; 
-- 分離tst資料庫
EXEC sp_detach_db 
      @dbname = ‘test‘,
      @skipchecks = ‘true‘

-- 把拷貝出來的 .mdf 檔案和 .ldf 檔案作為 test 資料庫的資料檔案掛上去
EXEC sp_attach_db

 
      @dbname = ‘test‘, 
      @filename1 = ‘e:\db\test.mdf‘, 
      @filename2 = ‘e:\db\test.ldf‘

-- 如果不想使用原來的 .ldf 檔案，也可以單獨掛 .mdf 檔案
EXEC sp_attach_single_file_db

 
      @dbname = ‘test‘, 
      @physname = ‘e:\db\test.mdf‘

 

如果當前資料庫處於生產環境不允許關閉服務的話，可以使用 BACKUP / RESTORE 的方法來完成複製工作：

 

-- 把資料庫名為realdb的Database Backup到指定的磁碟檔案
-- 用 WITH FORMAT 參數對bak檔案進行格式化，以便進行完整重寫
BACKUP DATABASE realdb TO DISK = ‘E:\db\BAK_FOR_realdb.bak‘ WITH FORMAT
-- 用 RESTORE FILELISTONLY 命令查看Database Backup檔案裡面的
-- 資料檔案和記錄檔的邏輯名稱，用於下一步的命令
RESTORE FILELISTONLY FROM DISK = ‘E:\db\BAK_FOR_realdb.bak‘

-- 使用備份檔案建立一個新的資料庫， realdb_dat 和 realdb_log
-- 分別是上面使用 RESTORE FILELISTONLY 命令查出的資料檔案邏輯
-- 名稱
RESTORE DATABASE test
    FROM DISK = ‘E:\db\BAK_FOR_realdb.bak‘
    WITH MOVE ‘realdb_dat‘ TO ‘E:\db\test.mdf‘,
    MOVE ‘realdb_log‘ TO ‘E:\db\test.ldf‘
GO