修改数据文件和日志文件路径

环境及需求
操作系统：Microsoft Windows Server 2008 R2 Enterprise
数据库：Microsoft SQL Server 2008 R2 (SP3)
服务器增加Fusion-io存储，需将数据库文件迁移到Fusion-io
迁移文件必定涉及数据库服务的重启，给定维护时间1小时。服务器上只安装一个数据库实例，实例下面有33个用户数据库(一个小游戏对应一个库)，部分数据库有多个数据文件。考虑到要迁移的数据文件有107个，最佳方式是使用：

ALTER DATABASE database_name 
MODIFY FILE (NAME = logical_file_name, FILENAME = 'os_file_name' )

迁移步骤
禁用数据库端口->停止数据库代理服务->备份数据库->创建新目录->修改系统目录文件位置->停止数据库服务->拷贝文件->重启数据库服务->检查
创建新目录
ALTER DATABASE...MODIFY FILE...到新路径的前提是目标路径存在。所以首先要创建新目录，之前数据库文件的存放位置是D:\SQLData\DBname\，33个数据库对应有33个子文件夹，手动一个个去创建略显笨拙。可使用下面的命令来创建：

if exist E:\SQLData\mklist.txt del E:\SQLData\mklist.txt
dir D:\SQLData\ /AD /B >>E:\SQLData\mklist.txt
for /f %%i in (E:\SQLData\mklist.txt) do (mkdir "%%i")
pause

将上述脚本保存到E:\SQLData\mkdir.cmd，双击bat文件即可。
修改系统目录文件位置
1、查看数据库基本信息

--1、查看数据库基本信息
SELECT name,database_id,user_access_desc,state_desc,recovery_model_desc,log_reuse_wait_desc,is_encrypted
FROM sys.databases d
ORDER BY name

检查当前数据库状态、恢复模式、日志空间重用等待类型

2、确定数据库的逻辑文件名称以及在磁盘上的当前位置

--2、确定数据库的逻辑文件名称以及在磁盘上的当前位置
SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 6
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

查询各个数据库的逻辑名称及物理存放位置

文件总个数达107个，存放在D:\SQLData\DBname\目录
3、使用 ALTER DATABASE 更改每个文件的位置(更改前需创建新目录)

--3、使用 ALTER DATABASE 更改每个文件的位置(更改前需创建新目录)
USE master;
GO
--DBA_Monitor
ALTER DATABASE DBA_Monitor
MODIFY FILE (NAME = DBA_Monitor, FILENAME = 'E:\SQLData\DBA_Monitor\DBA_Monitor.mdf');
GO
ALTER DATABASE DBA_Monitor
MODIFY FILE (NAME = DBA_Monitor_log, FILENAME = 'E:\SQLData\DBA_Monitor\DBA_Monitor_log.ldf');
GO
--tempdb
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\TempDB\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\TempDB\templog.ldf');
GO
--......
--107个文件路径从第2步获取，然后将D:\SQLData\改成E:\SQLData\
--......

注意：上面语句执行后再次从系统目录(step2)查看文件位置是否正确

4、停止SQL Server服务、拷贝数据库文件、启动服务(重启之前使用修改前的旧路径,tempdb不需拷贝)
5、使用步骤1、2查看数据库状态、数据文件路径(路径问题基本可以从ERRORLOG中查出)
第一次操作完成了后，查看数据库ERRORLOG发现如下错误

发现所有迁移到E盘的数据库都无法打开，检查数据库服务启动帐户为NT AUTHORITY\LOCALSERVICE，修改启动帐户为LocalSystem，再次重启成功。
附上正式操作前的测试脚本：

--创建测试数据库
USE [master]
GO
CREATE DATABASE [Test1] ON  PRIMARY 
( NAME = N'Test1', FILENAME = N'D:\SQLData\Test1\Test1.mdf' )
 LOG ON 
( NAME = N'Test1_log', FILENAME = N'D:\SQLData\Test1\Test1_log.ldf')
GO
CREATE DATABASE [Test2] ON  PRIMARY 
( NAME = N'Test2', FILENAME = N'D:\SQLData\Test2\Test2.mdf' )
 LOG ON 
( NAME = N'Test2_log', FILENAME = N'D:\SQLData\Test2\Test2_log.ldf')
GO
CREATE DATABASE [Test3] ON  PRIMARY 
( NAME = N'Test3', FILENAME = N'D:\SQLData\Test3\Test3.mdf' )
 LOG ON 
( NAME = N'Test3_log', FILENAME = N'D:\SQLData\Test3\Test3_log.ldf')
GO
CREATE DATABASE [Test4] ON  PRIMARY 
( NAME = N'Test4', FILENAME = N'D:\SQLData\Test4\Test4.mdf' )
 LOG ON 
( NAME = N'Test4_log', FILENAME = N'D:\SQLData\Test4\Test4_log.ldf')
GO
CREATE DATABASE [Test5] ON  PRIMARY 
( NAME = N'Test5', FILENAME = N'D:\SQLData\Test5\Test5.mdf' ), 
( NAME = N'Test51', FILENAME = N'D:\SQLData\Test5\Test51.ndf'), 
( NAME = N'Test52', FILENAME = N'D:\SQLData\Test5\Test52.ndf'), 
( NAME = N'Test53', FILENAME = N'D:\SQLData\Test5\Test53.ndf')
 LOG ON 
( NAME = N'Test5_log', FILENAME = N'D:\SQLData\Test5\Test5_log.ldf')
GO


/******操作前备份所有用户数据库及系统数据库******/
--1、查看数据库基本信息
SELECT name,database_id,user_access_desc,state_desc,recovery_model_desc,log_reuse_wait_desc,is_encrypted 
FROM sys.databases d
ORDER BY name

--2、确定数据库的逻辑文件名称以及在磁盘上的当前位置
SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

--3、使用 ALTER DATABASE 更改每个文件的位置(更改前需创建新目录)
USE master;
GO
--Test1
ALTER DATABASE Test1
MODIFY FILE (NAME = Test1, FILENAME = 'E:\SQLData\Test1\Test1.mdf');
GO
ALTER DATABASE Test1
MODIFY FILE (NAME = Test1_log, FILENAME = 'E:\SQLData\Test1\Test1_log.ldf');
GO
--Test2
ALTER DATABASE Test2
MODIFY FILE (NAME = Test2, FILENAME = 'E:\SQLData\Test2\Test2.mdf');
GO
ALTER DATABASE Test2
MODIFY FILE (NAME = Test2_log, FILENAME = 'E:\SQLData\Test2\Test2_log.ldf');
GO
--Test3
ALTER DATABASE Test3
MODIFY FILE (NAME = Test3, FILENAME = 'E:\SQLData\Test3\Test3.mdf');
GO
ALTER DATABASE Test3
MODIFY FILE (NAME = Test3_log, FILENAME = 'E:\SQLData\Test3\Test3_log.ldf');
GO
--Test4
ALTER DATABASE Test4
MODIFY FILE (NAME = Test4, FILENAME = 'E:\SQLData\Test4\Test4.mdf');
GO
ALTER DATABASE Test4
MODIFY FILE (NAME = Test4_log, FILENAME = 'E:\SQLData\Test4\Test4_log.ldf');
GO
--Test5
ALTER DATABASE Test5
MODIFY FILE (NAME = Test5, FILENAME = 'E:\SQLData\Test5\Test5.mdf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test5_log, FILENAME = 'E:\SQLData\Test5\Test5_log.ldf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test51, FILENAME = 'E:\SQLData\Test5\Test51.ndf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test52, FILENAME = 'E:\SQLData\Test5\Test52.ndf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test53, FILENAME = 'E:\SQLData\Test5\Test53.ndf');
GO
--注意：上面语句执行后注意从系统目录查看文件位置是否正常

--4、停止SQL Server服务、拷贝数据库文件、启用服务(重启之前使用修改前的旧路径,tempdb不需拷贝)
 
--5、使用步骤1、2查看数据库状态、数据文件路径(路径问题基本可以从ERRORLOG中查出)


----特殊问题处理
USE master;
GO
--数据文件用新名称(测试正常)
ALTER DATABASE Test1
MODIFY FILE (NAME = Test1, FILENAME = 'D:\SQLData\Test1\Test1_new.mdf');
GO
ALTER DATABASE Test1
MODIFY FILE (NAME = Test1_log, FILENAME = 'D:\SQLData\Test1\Test1_log_new.ldf');
GO
--停止SQL Server服务、拷贝数据库文件、修改数据文件名为上面脚本中的名称、启用服务


--数据文件逻辑名称和物理名称带"."(测试正常)
--先用GUI给Test5添加逻辑名称为'Test5.T1.54'路径为'E:\SQLData\Test5\Test5.T1.54.ndf'的数据库文件
ALTER DATABASE Test5
MODIFY FILE (NAME = Test5, FILENAME = 'D:\SQLData\Test5\Test5.mdf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test5_log, FILENAME = 'D:\SQLData\Test5\Test5_log.ldf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test51, FILENAME = 'D:\SQLData\Test5\Test51.ndf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test52, FILENAME = 'D:\SQLData\Test5\Test52.ndf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = Test53, FILENAME = 'D:\SQLData\Test5\Test53.ndf');
GO
ALTER DATABASE Test5
MODIFY FILE (NAME = N'Test5.T1.54', FILENAME = 'D:\SQLData\Test5\Test5.T1.54.ndf');
GO

----也可以使用分离附加
--分离数据库
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'Test5'
GO

--附加数据库(影响数据库创建时间)
USE [master]
GO
CREATE DATABASE [Test5] ON 
( FILENAME = N'E:\SQLData\Test5.mdf' ),
( FILENAME = N'E:\SQLData\Test5_log.ldf' ),
( FILENAME = N'E:\SQLData\Test51.ndf' ),
( FILENAME = N'E:\SQLData\Test52.ndf' ),
( FILENAME = N'E:\SQLData\Test53.ndf' ),
( FILENAME = N'E:\SQLData\Test5.T1.54.ndf' )
 FOR ATTACH
GO

--删除测试数据库
USE MASTER
GO
DROP DATABASE [Test1]
DROP DATABASE [Test2]
DROP DATABASE [Test3]
DROP DATABASE [Test4]
DROP DATABASE [Test5]
GO

当然正式环境也可以选择分离、COPY、附加的方式；也可以使用备份还原的方式，但这样操作的工作量(脚本数量会多些)，而且附加的数据库创建日期会更新(介意者建议选用ALTER DATABASE...MODIFY FILE...)。本次操作10:00开始，10:21完成，之后进行必要的检查，10:26开启数据库代理服务、启用数据库端口，10:29用户注册数据写入。
使用GUI备份/还原数据库，或使用语句备份/还原但未指定WITH STATS=?时，可通过下面语句大致知晓备份/还原进度：

SELECT percent_complete,start_time,status,command,database_id,wait_type,last_wait_type,estimated_completion_time
  FROM sys.dm_exec_requests
 WHERE session_id=61

percent_complete表示当前完成百分比，estimated_completion_time估计剩余时间