sql server数据库分离与附加语句 sql数据库的分离和附加

转载

mob64ca1417736e 2023-11-06 19:37:39

文章标签 数据库运维附加数据库日志文件 文章分类 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

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