SQL Server 移動資料庫

下面的示例將 tempdb 資料和日誌檔案移動到一個新位置，作為預先安排的重定位的一部分。

1. 確定 tempdb 資料庫的邏輯檔名稱以及在磁碟上的當前位置。

   SELECT name, physical_name AS CurrentLocation
   FROM sys.master_files
   WHERE database_id = DB_ID(N'tempdb');
   GO
   

2. 使用 ALTER DATABASE 更改每個檔案的位置。

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   GO
   

3. 停止再重新啟動 SQL Server 的例項。

4. 驗證檔案更改。

   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'tempdb');
   

5. 將 tempdb.mdf 和 templog.ldf 檔案從其原始位置刪除。

   
   

   
   

   在 SQL Server 中，通過在 ALTER DATABASE 語句的 FILENAME 子句中指定新的檔案位置，可以將使用者資料庫中的資料、日誌和全文目錄檔案移動到新位置。此方法適用於在同一 SQL Server 例項中移動資料庫檔案。若要將資料庫移動到另一個 SQL Server 例項或另一臺伺服器上，請使用備份和還原或分離和附加操作。

   注意
   SQL Server 資料庫引擎的某些功能改變了資料庫引擎在資料庫檔案中儲存資訊的方式。這些功能僅限於特定的 SQL Server 版本。不能將包含這些功能的資料庫移到不支援這些功能的 SQL Server 版本。使用 sys.dm_db_persisted_sku_features 動態管理檢視可列出當前資料庫中啟用的所有特定於版本的功能。

   本主題中的過程需要資料庫檔案的邏輯名稱。若要獲取該名稱，請在 sys.master_files 目錄檢視中查詢名稱列。

   注意
   將資料庫移動到另一個伺服器例項上時，若要為使用者和應用程式提供一致的體驗，您可能需要為資料庫重新建立部分或全部後設資料。有關詳細資訊，請參閱當資料庫在其他伺服器例項上可用時管理後設資料。

   計劃的重定位過程

   ---

   若要將移動資料或日誌檔案作為計劃的重定位的一部分，請執行下列步驟：

   1. 執行以下語句。

      ALTER DATABASE database_name SET OFFLINE ;

      
      
      

   2. 將檔案移動到新位置。

   3. 對於已移動的每個檔案，請執行以下語句。

      ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ) ;

      
      
      

   4. 執行以下語句。

      ALTER DATABASE database_name SET ONLINE ;

   5. 通過執行以下查詢來驗證檔案更改。

      SELECT  name ,
              physical_name AS CurrentLocation ,
              state_desc
      FROM    sys.master_files
      WHERE   database_id = DB_ID(N'<database_name>') ;

      
      
   計劃的磁碟維護的重定位

   ---

   若要將重定位檔案作為計劃的磁碟維護過程的一部分，請執行下列步驟：

   1. 對於要移動的每個檔案，請執行以下語句。

      ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
      

   2. 停止 SQL Server 例項或關閉系統以執行維護。有關詳細資訊，請參閱停止服務。

   3. 將檔案移動到新位置。

   4. 重新啟動 SQL Server 例項或伺服器。有關詳細資訊，請參閱啟動和重新啟動服務。

   5. 通過執行以下查詢來驗證檔案更改。

      SELECT  name ,
              physical_name AS CurrentLocation ,
              state_desc
      FROM    sys.master_files
      WHERE   database_id = DB_ID(N'<database_name>') ;

      
      
   故障恢復過程

   ---

   如果由於硬體故障而必須移動檔案，則請執行下列步驟，將檔案重新定位到一個新位置。

   重要提示
   如果資料庫無法啟動，即處於可疑模式下或處於未恢復狀態，則只有 sysadmin 固定角色的成員才可以移動該檔案。

   1. 如果啟動了 SQL Server 例項，則將其停止。

   2. 通過在命令提示符下輸入下列命令之一，在僅 master 恢復模式下啟動 SQL Server 例項。

      • 對於預設的 (MSSQLSERVER) 例項，請執行以下命令。

        NET START MSSQLSERVER /f /T3608

        
        
        

      • 對於命名例項，請執行以下命令。

        NET START MSSQL$instancename /f /T3608
        

      有關詳細資訊，請參閱如何啟動 SQL Server 例項（net 命令）。

   3. 對於要移動的每個檔案，請使用 sqlcmd 命令或 SQL Server Management Studio 執行以下語句。

      ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
      

      有關如何使用 sqlcmd 實用工具的詳細資訊，請參閱使用 sqlcmd 實用工具。

   4. 退出 sqlcmd 實用工具或 SQL Server Management Studio。

   5. 停止 SQL Server 例項。

   6. 將檔案移動到新位置。

   7. 啟動 SQL Server 例項。例如，執行 NET START MSSQLSERVER。

   8. 通過執行以下查詢來驗證檔案更改。SELECT name, physical_name AS CurrentLocation, state_desc

      FROM sys.master_files
      WHERE database_id = DB_ID(N'<database_name>');
      

   移動全文目錄

   ---

   若要移動全文目錄，請執行下列步驟。請注意，指定新的目錄位置時，只指定 new_path，而不是指定 new_path/os_file_name。

   1. 執行以下語句。

      ALTER DATABASE database_name SET OFFLINE
      

   2. 將全文目錄移動到新位置。

   3. 執行下列語句，其中：logical_name 是 sys.database_files 中 name 列的值，new_path 是目錄的新位置。

      ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path');
      

   4. 執行以下語句。

      ALTER DATABASE database_name SET ONLINE;
      

   另外，也可以使用 CREATE DATABASE 語句的 FOR ATTACH 子句移動全文目錄。下面的示例在 AdventureWorks2008R2 資料庫中建立一個全文目錄。若要將全文目錄移動到新位置，請分離 AdventureWorks2008R2 資料庫，並將全文目錄從物理意義上移動到新位置。然後附加資料庫，並指定全文目錄的新位置。

   USE AdventureWorks2008R2 ;
   CREATE FULLTEXT CATALOG AdvWksFtCat AS DEFAULT ;
   GO
   USE master ;
   GO
   --Detach the AdventureWorks2008R2 database.
   sp_detach_db AdventureWorks2008R2 ;
   GO
   --Physically move the full-text catalog to the new location.
   --Attach the AdventureWorks2008R2 database and specify the new location of the full-text catalog.
   CREATE DATABASE AdventureWorks2008R2 ON 
       (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_Data.mdf'), 
       (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_log.ldf'),
       (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat') FOR ATTACH ;
   GO

   
   
   示例

   ---

   下面的示例將 AdventureWorks2008R2 日誌檔案移動到一個新位置，作為計劃的重定位的一部分。

   USE master ;
   GO
   -- Return the logical file name.
   SELECT  name ,
           physical_name AS CurrentLocation ,
           state_desc
   FROM    sys.master_files
   WHERE   database_id = DB_ID(N'AdventureWorks2008R2')
           AND type_desc = N'LOG' ;
   GO
   ALTER DATABASE AdventureWorks2008R2 SET OFFLINE ;
   GO
   -- Physically move the file to a new location.
   -- In the following statement, modify the path specified in FILENAME to
   -- the new location of the file on your server.
   ALTER DATABASE AdventureWorks2008R2 
   MODIFY FILE ( NAME = AdventureWorks2008R2_Log, 
   FILENAME = 'C:\NewLoc\AdventureWorks2008R2_Log.ldf') ;
   GO
   ALTER DATABASE AdventureWorks2008R2 SET ONLINE ;
   GO
   --Verify the new location.
   SELECT  name ,
           physical_name AS CurrentLocation ,
           state_desc
   FROM    sys.master_files
   WHERE   database_id = DB_ID(N'AdventureWorks2008R2')
           AND type_desc = N'LOG' ;