开发者社区> 杰克.陈> 正文

SQL Server 移动数据库

简介: 原文: SQL Server 移动数据库 移动系统数据库在下列情况下可能很有用: 故障恢复。例如,数据库处于可疑模式下或因硬件故障而关闭。 计划的重定位。
+关注继续查看
原文: SQL Server 移动数据库

移动系统数据库在下列情况下可能很有用:

  • 故障恢复。例如,数据库处于可疑模式下或因硬件故障而关闭。

  • 计划的重定位。

  • 为预定的磁盘维护操作而进行的重定位。

下列过程适用于在同一 SQL Server 实例内移动数据库文件。若要将数据库移动另一个 SQL Server 实例中或另一台服务器上,请使用备份和还原分离和附加操作。

本主题中的过程需要数据库文件的逻辑名称。若要获取该名称,请在 sys.master_files 目录视图中查询名称列。

 

预先安排的重定位与预定的磁盘维护过程

若要将移动系统数据库数据或日志文件的操作作为预先安排的重定位或预定的维护操作的一部分,请执行下列步骤。此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。

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


如果移动了 msdb 数据库并为数据库邮件配置了 SQL Server 实例,则请完成下列附加步骤。

  1. 通过运行以下查询,验证是否已为 msdb 数据库启用 Service Broker。

    SELECT  is_broker_enabled
    FROM    sys.databases
    WHERE   name = N'msdb' ;


    有关启用 Service Broker 的详细信息,请参阅 ALTER DATABASE (Transact-SQL)

  2. 通过发送测试邮件验证数据库邮件是否正常运行。有关详细信息,请参阅对数据库邮件进行故障排除

如果由于硬件故障而必须移动文件,则请执行下列步骤,将文件重新定位到一个新位置。此过程适用于除 master 和 Resource 数据库以外的所有系统数据库。

 重要提示

如果数据库无法启动,即处于可疑模式下或处于未恢复状态,则只有 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 实例。例如,运行 NET STOP MSSQLSERVER

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

若要移动 master 数据库,请按下列步骤进行操作。

  1. “开始”菜单中,依次指向“所有程序”Microsoft SQL Server 和“配置工具”,再单击 SQL Server 配置管理器

  2. “SQL Server 服务”节点中,右键单击 SQL Server 实例(如 SQL Server (MSSQLSERVER)),并选择“属性”

  3. “SQL Server (实例名) 属性”对话框中,单击“高级”选项卡。

  4. 编辑“引导参数”值以指向 master 数据库数据和日志文件的计划位置,然后单击“确定”。可以选择移动错误日志文件。

    数据文件的参数值必须跟在 -d 参数的后面,日志文件的参数值必须跟在 -l 参数的后面。下面的示例显示 master 数据和日志文件默认位置的参数值。

    -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\
    master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
    LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
    DATA\mastlog.ldf
    

    如果 master 数据和日志文件预先安排的重定位是 E:\SQLData,则参数值将更改为:

    -dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
    
  5. 通过右键单击实例名称并选择“停止”,停止 SQL Server 实例。

  6. 将 master.mdf 和 mastlog.ldf 文件移动到新位置。

  7. 重新启动 SQL Server 实例。

  8. 通过运行以下查询,验证 master 数据库的文件更改。

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

Resource 数据库的位置为 <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\。无法移动该数据库。

A. 移动 tempdb 数据库

下面的示例将 tempdb 数据和日志文件移动到一个新位置,作为预先安排的重定位的一部分。

注意

由于每次启动 SQL Server 实例时都将重新创建 tempdb,所以不必实际移动数据和日志文件。在步骤 3 中重新启动服务时,将在新位置中创建这些文件。在重新启动服务之前,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' ;


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
23938 0
SQL Server 移动数据库
原文: SQL Server 移动数据库 移动系统数据库在下列情况下可能很有用: 故障恢复。例如,数据库处于可疑模式下或因硬件故障而关闭。 计划的重定位。
1023 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
32578 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
14095 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
8349 0
SQL Server的六种数据移动方法
1.通过工具DTS的设计器进行导入或导出           DTS的设计器功能强大,支持多任务,也是可视化界面,容易操作,但知道的人一般不多,如果只是进行SQL   Server数据库中部分表的移动,用这种方法最好,当然,也可以进行全部表的移动。
806 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
12903 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载