【SQLServer】SQL SERVER 移动系统数据库

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

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

    2、计划的重定位。

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

二、移动系统数据库的步骤：

1、移动master数据库

(1).在“Start”菜单中，依次指向“All Programs”、”Microsoft SQL Server 2008 R2”和“Configuration Tools”，再单击 “sql Server Configuration Manager”。

(2).在“SQL Server Services”节点中，右键单击 SQL Server 实例（如 SQL Server (MSSQLSERVER)），并选择“Properties”。

(3).在“SQL Server(MSSQLSERVER) Properties”对话框中，单击“Advanced”选项卡。

(4).编辑“Startup Parameters”值以指向 master 数据库数据和日志文件的计划位置，然后单击“Apply”。可以选择移动错误日志文件。

数据文件的参数值必须跟在 -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:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA，则参数值将更改为：

-dE:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eE:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lE:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

(5).运行如下SQL语句，使数据文件的逻辑名字与实际文件对应。

alter database master

modify file (NAME = 'master', Filename = 'E:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf');

Go

alter database master

modify file (NAME = 'mastlog', Filename = 'E:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.ldf');

GO

(6). 通过右键单击实例名称并选择“Stop”，停止 SQL Server 实例。

(7).将 master.mdf 和 mastlog.ldf 文件移动到新位置(这个位置就是上面参数所指定的位置，即：E:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA)。

(8).重新启动 SQL Server 实例。

(9).通过运行以下查询，验证 master 数据库的文件更改。

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

2、移动其它系统数据库

注意：由于每次启动 SQL Server 实例时都将重新创建 tempdb，所以不必实际移动数据和日志文件。在重新启动服务时，将在新位置中创建这些文件。在重新启动服务之前，tempdb 将继续使用现有位置中的数据和日志文件。

以下为步骤：
(1). 确定系统数据库的逻辑文件名称以及在磁盘上的当前位置。

-- 如tempdb

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

(2). 使用 ALTER DATABASE 更改每个文件的位置。

alter database tempdb

modify file (NAME = 'tempdev', Filename = 'E:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

GO

alter database tempdb

modify file (NAME = 'templog', Filename = 'E:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf');

GO

(3). 停止服务并移动文件到更改位置

(4). 重新启动 SQL Server 的实例服务。

(5). 验证文件更改。

    SELECT name, physical_name AS CurrentLocation, state_desc

    FROM sys.master_files

    WHERE database_id = DB_ID('tempdb');

    GO

(6). 一般会将原位置文件保留一段时间进行功能验证，待验证完成，再将系统文件，如 tempdb.mdf 和 templog.ldf 文件从其原始位置删除。

二、备一个脚本，可以对多实例的时候使用：

select 'ALTER DATABASE ['+db_name([database_id])+'] MODIFY FILE(NAME='''+name+''',FILENAME='''+REPLACE([physical_name],'C:\Program Files\Microsoft SQL Server\','E:\SQLData\')+''')' from sys.master_files

where physical_name like 'c:%'

AND [database_id]<>1