sql中分离与附加数据库.txt

一个学生考试成绩管理的半成品

在SQL Server 中迁移数据库
本文摘自肖桂东编著的《SQL Server 疑难解析》

问题描述：
    由于硬盘分区空间不够，我想将一个数据库存放的位置从一个分区转移到另外的分区。请问我该如何进行操作？

问题分析： 
    在新建一个数据库的时候，我们可以分别指定其数据文件（.mdf 文件）以及事务日志文件（.ldf 文件）存放的位置，如果需要重新指定一个数据库存放的位置，对于一般用户数据库与系统数据库而言，要分别用不同的方法来实现。

问题解答：
    在 SQL Server 中，要将一个数据库存放的位置转移到另外的位置，可以利用数据库分离（sp_detach_db）和数据库附加操作（sp_attach_db）命令来完成。
    由于用户数据库和系统数据库的不同，分为以下几种情况来处理。
    （1）转移用户数据库。
    首先，使用下面语句分离数据库：
USE master
GO
EXEC sp_detach_db 'mydb'
GO
    接着，将该数据库的数据文件和日志文件从旧的位置（如 D:\MSSQL\Data）拷贝到新的位置（如E:\SQLData）。
    最后，使用下面语句重新附加数据库：
USE master
GO
EXEC sp_attach_db @dbname = 'mydb', 
       @filename1 = 'E:\Sqldata\mydbdata.mdf', 
       @filename2 = 'E:\Sqldata\mydblog.ldf'
GO
（2）转移master数据库。
    首先，由于 SQL Server 每次启动的时候都需要读取 master 数据库，因此我们必须在企业管理器中改变 SQL Server 启动参数中 master 数据文件和日志文件的路径。
    在 SQL Server 属性的“常规”选项卡下面有一个叫做【启动参数】的按钮，单击后可以看到右图所示的界面。
     其中，-d 用来指定 master 数据库文件的完全合法路径，而 –l用来指定 master 数据库日志文件的完全合法路径。
    将这些参数修改为新的 master 数据文件和日志文件所在的路径，如：
-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf
    接着，停止 SQL Server 服务，将 master.mdf 文件和 mastlog.ldf 文件拷贝到新的位置，如上例中的 E:\SQLData。
最后，只要重新启动服务即可。
    （3）转移msdb数据库和model数据库。
    我们同样可以使用 sp_detach_db 和 sp_attach_db 来分离和附加这两个数据库。但是对于 msdb 数据库，首先应确保 SQL Server 代理当前没有运行，否则 sp_detach_db 存储过程将运行失败，并返回如下消息： 
无法除去数据库 'msdb' ，因为它当前正在使用。
    接着，必须用跟踪标记 3608 启动 SQL Server，这样它不会恢复除 master之外的任何数据库。
要添加跟踪标记 3608 作为 SQL Server 的启动参数，首先调出如前所示的“启动参数”对话框，再添加新参数 -T3608。
    添加跟踪标记 3608 后，停止并重新启动 SQL Server，再如下分离 msdb 或者 model 数据库： 
EXEC sp_detach_db 'msdb'
GO
    接着，将数据文件和日志文件移到新的位置，然后再如下重新附加 msdb 数据库或者 model 数据库： 
USE master
GO
EXEC sp_attach_db @dbname = 'model',
       @filename1 = 'mdf 文件新的路径名',
       @filename2 = 'ldf 文件新的路径名'
GO
    最后，在企业管理器中，从“启动参数”框中删除 -T3608 跟踪标记，再停止并重新启动 SQL Server。
    （4）转移 tempdb 数据库。
    对于 tempdb 数据库而言，可以直接使用 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 = 
'E:\Sqldata\templog.ldf')
GO

知识点分析： 
    （1）只应对以前使用显式 sp_detach_db 操作从数据库服务器分离的数据库执行 sp_attach_db。如果必须指定多于 16 个文件，请使用带有 FOR ATTACH 子句的 CREATE DATABASE。
如果将数据库附加到的服务器不是该数据库从中分离的服务器，并且启用了分离的数据库以进行复制，则应该运行 sp_removedbreplication 从数据库删除复制。
    （2）启动参数是一组用来指定 SQL Server 服务启动时要启用的选项。错误的启动参数将导致 SQL Server 服务无法启动，而有时候为了实现一些高级的功能，例如数据库的迁移等，我们可以通过正确设置启动参数来实现。