一、移动系统数据库在下列情况下可能很有用:
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