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' ;