（4.20）sql server分离附加操作

关键词；sql server分离、sql server附加、分离附加、sql server附加分离

【0】、数据库分离、附加的说明

SQL Server提供了“分离/附加”数据库、“备份/还原”数据库、复制数据库等多种数据库的备份和恢复方法。这里介绍一种学习中常用的“分离/附加”方法，类似于大家熟悉的“文件拷贝”方法，即把数据库文件（.MDF）和对应的日志文件（.LDF）再拷贝到任何需要恢复这个数据库的系统磁盘上。

（1）分离数据库就是将某个数据库（如db_tank）从SQL Server数据库列表中删除，使其不再被SQL Server管理和使用，但该数据库的文件（.MDF）和对应的日志文件（.LDF）完好无损。分离成功后，我们就可以把该数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘中作为备份保存。

（2）附加数据库就是将一个备份磁盘中的数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到需要的计算机，并将其添加到某个SQL Server数据库服务器中，由该服务器来管理和使用这个数据库。

 

【1】使用T-SQL实现分离附加

【1.1】附加 

 

--附加
USE master
GO

DECLARE @new_path nvarchar(200),@new_log_path nvarchar(200)
DECLARE @is_file_exist INT; 


SET @new_path=N'D:\dandantang\data\'
SET @new_log_path=N'E:\dandantang\dblog\'

DECLARE @t_driver TABLE (Drive CHAR(1),MB_Free INT); -- 该表变量保存各分区的剩余空间信息
INSERT INTO @t_driver EXEC master.dbo.xp_fixeddrives; -- 填充分区剩余空间信息
                                                      
IF NOT EXISTS(SELECT 1 FROM @t_driver WHERE Drive ='e')
    SET @new_log_path = N'D:\dandantang\dblog\'

declare @file nvarchar(400),@log_file nvarchar(400)

select @file=@new_path+'Db_Tank_Data.mdf'
    ,@log_file=@new_log_path+'Db_Tank_log.ldf'

--核心语句，@filename1参数为mdf数据文件，@filename2参数为ldf日志文件
EXEC sp_attach_db @dbname = N'Db_Tank',  @filename1 =@file,  @filename2 = @log_file 


EXEC sp_dbcmptlevel Db_Tank,90
ALTER DATABASE Db_Tank SET PAGE_VERIFY CHECKSUMGO

ALTER DATABASE Db_Tank MODIFY FILE(NAME='Db_Tank_Data',FILEGROWTH=128MB) --设置文件自增值
ALTER DATABASE Db_Tank MODIFY FILE(NAME='Db_Tank_Log',FILEGROWTH=64MB) --设置文件自增值
GO

ALTER DATABASE Db_Tank SET MULTI_USER,OnlineGO

--只有mdf附加
CREATE DATABASE [Db_Tank] ON 
( FILENAME = N'D:\dandantang\data\Db_Tank_Data.mdf' )
FOR ATTACH



--有ndf的附加
USE [master]
GO
CREATE DATABASE [AuditDB] ON 
( FILENAME = N'D:\dandantang\data\AuditDB.mdf' ),
( FILENAME = N'E:\dandantang\dblog\AuditDB.LDF' ),
( FILENAME = N'D:\dandantang\data\auditlog.ndf' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'AuditDB' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [AuditDB].dbo.sp_changedbowner @loginame=N'GS_GN_405_GASIE\gnadit', @map=false
GO

 

 

【1.2】分离

USE [master]
GO


IF DB_ID('Db_Tank') IS NOT NULL
BEGIN
    ALTER DATABASE Db_Tank SET SINGLE_USER WITH ROLLBACK AFTER 1
    DBCC SHRINKDATABASE (Db_Tank, TRUNCATEONLY) --收缩日志
    EXEC sp_detach_db 'Db_Tank'
END
GO

 

 

【2】、使用SSMS分离附加

【2.1】分离（CRM库）

1. 在启动SSMS并连接到数据库服务器后，在对象资源管理器中展开服务器节点。在数据库对象下找到需要分离的数据库名称，这里以CRM数据库为例。右键单击CRM数据库，在弹出的快捷菜单中选择属性。

2. 在“数据库属性”窗口左边“选择页”下面区域中选定“选项”对象，然后右边区域的“其它选项”列表中找到“状态”项，单击“限制访问”文本框，在其下拉列表中选择“SINGLE_USER”。

3. 在上图中单击“确定”按钮后将出现一个消息框，通知我们此操作将关闭所有与这个数据库的连接，是否继续这个操作（如下图）。注意：在大型数据库系统中，随意断开数据库的其它连接是一个危险的动作，因为我们无法知道连接到数据库上的应用程序正在做什么，也许被断开的是一个正在对数据复杂更新操作、且已经运行较长时间的事务。

4. 单击“是”按钮后，数据库名称后面增加显示“单个用户”（如下图）。右键单击该数据库名称，在快捷菜单中选择“任务”的二级菜单项“分离”。出现下图所示的“分离数据库”窗口。

5. 在下图的分离数据库窗口中列出了我们要分离的数据库名称。请选中“更新统计信息”复选框。若“消息”列中没有显示存在活动连接，则“状态”列显示为“就绪”；否则显示“未就绪”，此时必须勾选“删除连接”列的复选框。

6. 分离数据库参数设置完成后，单击底部的”确定”按钮，就完成了所选数据库的分离操作。这时在对象资源管理器的数据库对象列表中就见不到刚才被分离的数据库名称CRM了（如下图）。

【2.2】、附加数据库（CRM库）

1. 将需要附加的数据库文件和日志文件拷贝到某个已经创建好的文件夹中。

2. 在下图窗口中，右击数据库对象，并在快捷菜单中选择“附加”命令，打开“附加数据库”窗口。

3. 在“附加数据库”窗口中，单击页面中间的“添加”按钮，打开定位数据库文件的窗口，在此窗口中定位刚才拷贝到SQL Server的DATA文件夹中的数据库文件目录（数据文件不一定要放在“DATA”目录中），选择要附加的数据库文件（后缀.MDF，如下图）。

4. 单击“确定”按钮就完成了附加数据库文件的设置工作。这时，在附加数据库窗口中列出了需要附加数据库的信息（如下图）。如果需要修改附加后的数据库名称，则修改“附加为”文本框中的数据库名称。我们这里均采用默认值,因此，单击确定按钮，完成数据库的附加任务。

完成以上操作，我们在SSMS的对象资源管理器中就可以看到刚刚附加的数据库CRM（如下图）。

以上操作可以看出，如果要将某个数据库迁移到同一台计算机的不同SQL Server实例中或其它计算机的SQL Server系统中，分离和附加数据库的方法是很有用的。

【2.3】、只有MDF文件附加

　

　　只有MDF的情况下，红色框中，日志文件在消息列会提示找不到文件/文件不存在，我们直接选中ldf日志文件这一行，点击右下角的删除即可。

　　sql server会根据MDF，重新自动生成一个新的LDF。

【3】深入附加MDF（实践）

（3.1）本机MDF附加

由于种种原因，我们如果当时仅仅备份了mdf文件，那么恢复起来就是一件很麻烦的事情了。

如果您的mdf文件是当前数据库产生的，那么很侥幸，也许你使用sp_attach_db或者sp_attach_single_file_db可以恢复数据库，但是会出现类似下面的提示信息

　　设备激活错误。物理文件名 'C:/Program Files/Microsoft SQL Server/MSSQL/data/test_Log.LDF' 可能有误。

　　已创建名为 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.LDF' 的新日志文件。

（3.2）跨机器实例 mdf 附加

但是，如果您的数据库文件是从其他计算机上复制过来的，那么很不幸，也许上述办法就行不通了。你也许会得到类似下面的错误信息

　　服务器: 消息 1813，级别 16，状态 2，行 1

　　未能打开新数据库 'test'。CREATE DATABASE 将终止。

　　设备激活错误。物理文件名 'd:/test_log.LDF' 可能有误。

怎么办呢？别着急，下面我们举例说明恢复办法。

　　A．我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server Enterprise Manager里面建立。

　　B．停掉数据库服务器。

　　C．将刚才生成的数据库的日志文件test_log.ldf删除，用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。

　　D．启动数据库服务器。此时会看到数据库test的状态为“置疑”。这时候不能对此数据库进行任何操作。

　　E．设置数据库允许直接操作系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器，按右键，选择“属性”，在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现。

use master
go
sp_configure 'allow updates',1
go
reconfigure with override
go

F．设置test为紧急修复模式

update sysdatabases set status=-32768 where dbid=DB_ID('test')

此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读/置疑/脱机/紧急模式”可以看到数据库里面的表，但是仅仅有系统表

G．下面执行真正的恢复操作，重建数据库日志文件

dbcc rebuild_log('test','C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.ldf')
执行过程中，如果遇到下列提示信息：
服务器: 消息 5030，级别 16，状态 1，行 1
未能排它地锁定数据库以执行该操作。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

说明您的其他程序正在使用该数据库，如果刚才您在F步骤中使用SQL Server Enterprise Manager打开了test库的系统表，那么退出SQL Server Enterprise Manager就可以了。

正确执行完成的提示应该类似于：

　　警告: 数据库 'test' 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项，并且可能需要删除多余的日志文件。

　　DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为“只供DBO使用”。此时可以访问数据库里面的用户表了。

H．验证数据库一致性（可省略）

dbcc checkdb('test')

一般执行结果如下：

　　CHECKDB 发现了 0 个分配错误和 0 个一致性错误（在数据库 'test' 中）。

　　DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

I．设置数据库为正常状态

sp_dboption 'test','dbo use only','false'

如果没有出错，那么恭喜，现在就可以正常的使用恢复后的数据库啦。

J．最后一步，我们要将步骤E中设置的“允许对系统目录直接修改”一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然，我们可以在SQL Server Enterprise Manager里面恢复，也可以使用如下语句完成

sp_configure 'allow updates',0
go
reconfigure with override
go

 

说到底还是这几个语句起了作用：(ExamMIS==test)

pdate sysdatabases set status=-32768 where dbid=DB_ID('ExamMIS')

dbcc rebuild_log('ExamMIS','C:/Program Files/Microsoft SQL Server/MSSQL/Data/ExamMIS_log.ldf')

dbcc checkdb('ExamMIS')

sp_dboption 'ExamMIS','dbo use only','false'

 

【参考文章】

　　https://www.cnblogs.com/xqzt/p/5734579.html