4.13 移动或复制一个数据库

4.13  移动或复制一个数据库

我们也许要在系统执行维护任务之前，发生硬件故障之后，或者将当前硬件置换为更新更快的系统时移动一个数据库。复制数据库是创建一个备用开发环境或测试环境常用的方法。我们可以通过一种称为“分离和附加”（detach和attach）的方法，或者通过备份数据库，然后将它还原到一个新的位置来移动或复制数据库。

分离和重新附加数据库

我们可以通过使用一个简单的存储过程从服务器分离一个数据库。分离数据库时必须保证没有用户正在使用数据库。如果发现无法终止已存在连接，可以使用ALTER DATABASE命令，并利用一个能够中断已存在连接的终止选项把数据库设置到SINGLE_USER模式。分离数据库保证数据库中没有不完整的事务，也没有留在内存中的脏数据页面。如果不能满足这些条件，分离操作就不会成功。一旦一个数据库被分离，它所对应的条目就会从sys.databases目录视图和下面的系统表中删除。

下面是分离一个数据库的命令：

Exec sp_detach_db ;

一旦这个数据库被分离，从SQL Server的角度来看这和删除这个数据库没有什么不同。在SQL Server实例中不会留有这个数据库的痕迹。如果我们计划稍后重新附加这个数据库，那么最好能够将这个数据库中所有文件的各个属性记录下来。

注意： DROP DATABASE命令将一个数据库的所有痕迹从SQL Server实例中删除，但是删除一个数据库更为严重。SQL Server在删除数据库之前先要确定没有人连接该数据库，但它却并不检查赃数据页和活动事务。删除一个数据库也会从操作系统删除它所对应的各个物理文件，所以除非我们有备份，否则就真的失去了这个数据库。

为了附加一个数据库，我们可以使用sp_attach_db存储过程，或者使用带有FOR ATTACH选项的CREATE DATABASE命令。在SQL Server 2005中推荐使用CREATE DATABASE，因为它能提供更多对所有文件和它们的布置的控制，并且sp_attach_db正在被淘汰。使用sp_attach_db的限制是16个文件。CREATE DATABASE则没有这个限制——实际上，可以为每个数据库指定多达32 767个文件和32 767个文件组。

    
CREATE DATABASE database_name
ON  [ ,...n ]
FOR { ATTACH
| ATTACH_REBUILD_LOG }

注意只有主文件被要求使用条目，因为主文件包含有关于所有其他文件位置的信息。如果我们要附加已存在的文件到一个与该数据库首次创建或最后附加时不同的路径，我们必须使用额外的条目。在任何情况下数据库的所有数据文件都必

须可用，不论它们是否在CREATE DATABASE命令中被指定。如果有多个日志文件，那么它们也必须全部可用。

然而，如果一个读/写数据库含有当前不可用的单个日志文件，如果该数据库在附加操作之前，在没有用户和活动事务的情况下被关闭，那么FOR ATTACH会重建该日志文件并更新主文件中的有关日志的信息。如果该数据库是只读数据库，那么就不能更新主文件，所以也就不能重建日志。因此，当我们附加一个只读数据库时，必须在FOR ATTACH从句中指定日志文件。

或者，我们可以使用FOR ATTACH_REBUILD_LOG选项，这个选项指定了将会通过附加一组已存在的操作系统文件来创建数据库。该选项只限用于读/写数据库。如果一个或多个事务日志丢失，那么就需要重建日志。必须在条目指定主文件。另外，如果日志文件可用，那么SQL Server将会使用那些日志文件而不是重建日志文件，所以这时使用FOR ATTACH_REBUILD_LOG的作用就如同使用FOR ATTACH一样。

如果通过附加数据库重建了事务日志，使用FOR ATTACH_REBUILD_LOG会中断日志备份链。进行这种操作后应该考虑做一次数据库完全备份。

一般情况下，当我们复制一个带有大型日志的读/写数据库到另外一台服务器时，可以使用FOR ATTACH_REBUILD_LOG。条件是这台服务器将主要使用或只用该数据库副本进行读操作，并且因此需要的日志空间会比原来的数据库要小。

虽然文档指出sp_attach_db或CREATE DATABASE FOR ATTACH只应该用在我们之前使用sp_detach_db分离出的数据库上，但是有时也不必遵守文档。如果关闭SQL Server实例，那么文件也将会被关闭，这就好像已经分离了数据库一样。然而，这无法保证该数据库所有的脏数据页在SQL Server实例关闭前已经被写入磁盘。当附加的数据库是有着可用日志文件的数据库时，这并不构成一个问题。这个日志文件记录有所有已经完成的事务，并且当该数据库被附加时会进行一次完全恢复来保证数据库的一致性。使用sp_detach_db存储过程的一个好处就是SQL Server会知道这个数据库是被干净地关闭的，并且可用的日志文件也不是附加该数据库所必须的。SQL Server将会建立一个新的日志文件。因为sp_attach_db创建的新日志文件将会是最小的——还不到1MB，所以这是一种快速收缩一个已经长得太大的日志文件的方法。

备份和还原数据库

我们可以使用备份和还原来将一个数据库移动到一个新的位置，这可以作为detach和attach的替代方法。这种方法的好处是因为备份是完全在线的操作，所以数据库根本不需要离线。因为本书不是一本教数据库管理员“怎样去做”的书，您可以参考关联内容里的参考书目来寻找几本关于备份和还原数据库的优秀推荐图书，来了解为您的公司建立备份和还原计划的最优方法。不管怎样，因为一些与备份和还原过程有关的话题能够帮助我们理解为什么一个备份计划可能会比另外一个更加适合我们的需求，所以我们将在第5章讨论备份和还原。其中大多数话题会涉及备份和还原操作中事务日志的作用。

移动系统数据库

作为重新布置计划或安排好的维护操作的一部分，我们也许需要移动系统数据库。移动tempdb、model和msdb的步骤与移动master数据库或resource数据库的步骤稍有不同。
下面是移动一个没有损坏的系统数据库的步骤（此数据库不是master数据库或resource数据库）：

1.对数据库中每个要移动的文件使用带有MODIFY FILE选项的ALTER DATABASE命令来指定新的文件位置。

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

如果由于硬件故障而需要移动系统数据库，该解决方案是有点问题的。因为我们可能无法访问服务器来运行ALTER DATABASE命令。下面的步骤是移动一个损坏的系统数据库（除了master数据库或resource数据库之外）：

1.如果SQL Server实例已经启动，那么停止该实例。

2.通过在命令提示行下输入一条命令将该SQL Server实例启动到master-only恢复模式。

-- 如果该实例是默认实例：

NET START MSSQLSERVER /f /T3608

-- 如果是命名实例：

NET START MSSQL$instancename /f /T3608

3.对数据库中每个要移动的文件，使用带有MODIFY FILE选项的ALTER DATABASE命令来指定新的物理位置。可以使用SQL Server Management Studio或SQL CMD工具。

4.退出SQL Server Management Studio或SQLCMD工具。

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

移动master数据库和Resource数据库

resource数据库的位置（它的名字实际上是mssqlsystemresource）依赖于master数据库的位置。如果移动master数据库，那么就必须将resource数据库也移动到同一个目录中。
在联机丛书中能够找到移动这些特殊数据库的完整细节，这里对这些步骤作一个总结。移动这些数据库与移动其他系统数据库最大的不同在于我们必须通过SQL Server 配置管理器。
按照下面的步骤来移动master和resource数据库。

1.打开SQL Server配置管理器。右击目标SQL Server实例，选择属性，然后点击高级标签。

2.编辑各个启动参数值来指向新的master数据库数据和日志文件的目录位置。可以选择将SQL Server错误日志文件也移动过来。数据文件的参数值必须跟随-d参数，日志文件的参数值必须跟随-l参数，错误日志的参数值必须跟随-e参数，如下所示。

    
-dE:\SQLData\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; 
-lE:\SQLData\mastlog.ldf;
-eE:\ SQLData\LOG\ERRORLOG;

3.停止SQL Server实例并物理地移动master和mssqlsystemresource的文件到新的位置。

4.如前所示，通过使用/f和/T3608标记启动SQL Server实例到master-only恢复模式。

5.通过SQLCMD命令或SQL Server Management Studio，使用ALTER DATABASE语句更改mssqlsystemresource数据库的FILENAME路径使之与master数据文件的新位置匹配。一定不要更改该数据库的名称或文件名。

    
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME=
'new_path_of_master\mssqlsystemresource.mdf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME=
'new_path_of_master\mssqlsystemresource.ldf');

6.设置mssqlsystemresource数据库为只读，然后停止SQL Server实例。

7.移动resource数据库的数据和日志文件到新的位置。

8.重启SQL Server实例。

9.通过运行下面的查询来校验master数据库的文件更改。注意我们不能通过使用系统目录视图或系统表来查看resource数据库的元数据。

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

译注：作者自行修正。前后一致，是mytable而不是table1。
译注：作者自行修正。多余，删去。