Attach and Detach Database

Những câu lệnh t-sql thêm và loại bỏ database MS SQL Server

1. Attach Database ( Thêm database vào SQL SERVER)

Chú ý cần phân quyền cho file .MDF và .LDF có quyền thì mới attach vào được, ví dụ Add Everyone vào 2 file này.

Lệnh 1: hay dùng

-- Khai báo các biến hệ thống
Declare @database_name nvarchar(100); -- Tên database
Declare @pathdb nvarchar(300);   -- Đường dẫn thư mục databsae
Declare @sql nvarchar(max)=N'';  -- Câu lệnh sql query
Declare @err_state int =0;  -- Trạng thái lỗi trả về

-- Điền thông tin attach db vào:

set @database_name=N'DTECH_HN';   --Tên database cần Attach vào

set @pathdb=N'D:\DATA'   -- Đường dẫn thư mục chứa databse và log cần Attach vào

-- Thực thi câu lệnh
set @sql=@sql+'EXEC sp_attach_db @dbname = N'''+@database_name+''',
              @filename1 = N'''+@pathdb+'\'+@database_name+'.mdf'', 
              @filename2 =N'''+@pathdb+'\'+ @database_name +'_log.ldf'''
BEGIN TRY
    print @sql
	exec(@sql)
END TRY
BEGIN CATCH
    SELECT @err_state = ERROR_STATE()
END CATCH;

------------------------------------------------------------------------------------------------
if @err_state=3  -- Đã tồn tại Database cần attach vào rồi, thông báo, ko làm gì nữa
BEGIN
	Print N'Đã tồn tại database ['+@database_name+'] trên SQL SERVER.'
END
------------------------------------------------------------------------------------------------
if @err_state=101 -- Thực hiện câu lệnh Attach lại khi file mdf không có _LOG trong tên file
BEGIN
	-- Thực thi câu lệnh
	set @sql='EXEC sp_attach_db @dbname = N'''+@database_name+''',
				  @filename1 = N'''+@pathdb+'\'+@database_name+'.mdf'', 
				  @filename2 =N'''+@pathdb+'\'+ @database_name +'.ldf'''
	print @sql
	exec(@sql)
END

-- Thay giá trị ở biến @dbname, đường dẫn tới file mdf, ldf
EXEC sp_attach_db @dbname = N'Dtech2022_DEMO',   
    @filename1 =   
N'D:\DTECH\DTECHDEMO_MAY\DataSQL\Dtech2022_DEMO.mdf',   
    @filename2 =   
N'D:\DTECH\DTECHDEMO_MAY\DataSQL\Dtech2022_DEMO_log.ldf'; 

Lệnh 2:

-- Attach khôi phục lại file log
USE [master]
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'E:\MSSQL\TestDBCopy.mdf' )
 FOR ATTACH_REBUILD_LOG 
GO

-- Attach khôi phục lại ép buộc tạo được file log
USE master;

CREATE DATABASE [TestDB]
    ON  ( FILENAME = N'E:\MSSQL\TestDB_1.mdf' )
    FOR ATTACH_FORCE_REBUILD_LOG;
GO

Lệnh 3:

-- Attach thêm database với tuỳ chọn
USE [master]
GO

CREATE DATABASE [TestDB_Repair]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB_Repair_file1', 
			FILENAME = N'E:\MSSQL\TestDB_Repair_1.mdf',
			SIZE = 8MB , 
			MAXSIZE = UNLIMITED, 
			FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'TestDB_Repair_log_file1',
			 FILENAME = N'E:\MSSQL\TestDB_Repair_1.ldf',
			 SIZE = 8MB,
			 MAXSIZE = 2048GB,
			 FILEGROWTH = 32MB)
GO

2. Detach Database ( Gỡ bỏ database khỏi SQL SERVER)

Lệnh 1: hay dùng

-- Thay Dbname bằng tên database cần gỡ bỏ
EXEC sp_detach_db 'Dbname', 'true'; 

Lệnh 2: gỡ bỏ bỏ qua bước kiểm tra

USE [master]
GO
ALTER DATABASE [SQLShack] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLShack', @skipchecks = 'false'
GO

3. ATTACH , DETACH NHIỀU DATABASES

1. ATTACH NHIỀU DATABASE

• Cách 1: Thêm 1 Proc mới trên SQL và chạy lệnh gọi Proc đó với đường dẫn:

--Bước 1. Thêm Proc usp_MultiAttachSingleMDFFiles  ở phía dưới vào trước
--Bước 2. Sửa dụng Proc usp_MultiAttachSingleMDFFiles để chạy trên SQL Query:
-- Chú ý: C:\DTECH\DataSQL là đường dẫn thư mục chứa file .mdf và .ldf
-- Trước khi chạy thì cần chuyển file log .Ldf sang một thư mục khác hoặc xoá file log đi chỉ để file data .mdf để thực hiện
USE master
GO
EXEC dbo.usp_MultiAttachSingleMDFFiles 'C:\DTECH\DataSQL'   
GO 


---Proc usp_MultiAttachSingleMDFFiles cần thêm ở bước 1
USE master
GO
CREATE PROCEDURE [dbo].[usp_MultiAttachSingleMDFFiles] ( @mdfTempDir nvarchar(500) )
AS
BEGIN  
   DECLARE @dirstmt       nvarchar(1000)
   DECLARE @currFile      nvarchar(160)
   DECLARE @db_name       nvarchar(256)
   DECLARE @phys_name     nvarchar(520)
   DECLARE @dbccstmt      nvarchar(1000)
   DECLARE @db2attch_ver  INT
   DECLARE @curr_srv_ver  INT  
   DECLARE @mdfFileNames  TABLE (mdfFile nvarchar(260))
   DECLARE @mdfFileATTR   TABLE (attrName sql_variant, attrValue sql_variant)
   
   DECLARE cf CURSOR FOR SELECT mdfFile FROM @mdfFileNames

   SET NOCOUNT ON

   -- get all mdf file names only , in bare format.
   SET @dirstmt = 'dir /b "' + @mdfTempDir + '"\*.mdf'

   INSERT into @mdfFileNames 
   EXEC xp_cmdshell @dirstmt
   
   DELETE from @mdfFileNames where mdfFile IS NULL or mdfFile = 'File Not Found'

   -- if file is already attached skip it
   DELETE FROM @mdfFileNames 
   WHERE mdfFile IN (SELECT mdfFile FROM @mdfFileNames a INNER JOIN sys.master_files b ON lower(@mdfTempDir + '\' + a.mdfFile) = lower(b.physical_name) )

   -- if no files exist then exit process
   IF not exists (SELECT TOP 1 * FROM @mdfFileNames)
   BEGIN
      PRINT 'No files found to process'
      RETURN
   END

   -- get the current server database version
   SELECT  @curr_srv_ver = CONVERT (int,DATABASEPROPERTYEX('master', 'version'))

   BEGIN TRY

      OPEN cf

      FETCH NEXT FROM cf INTO @currFile

      WHILE @@FETCH_STATUS = 0 
      BEGIN 
 
         SET @phys_name =  @mdfTempDir + '\' + @currFile
         SET @dbccstmt = 'DBCC checkprimaryfile (' + '"' + @phys_name + '"' + ',2)'

         INSERT INTO @mdfFileATTR 
         EXEC (@dbccstmt)
         
         SELECT @db_name = convert (nvarchar(256), attrValue)
         FROM @mdfFileATTR
         WHERE attrName = 'Database name'
      
         -- get the candidate to be attached db version
         SELECT @db2attch_ver = convert (int, attrValue)
         FROM @mdfFileATTR
         WHERE attrName = 'Database version'

         -- if the current server database version is less that the attached db version 
         -- OR
         -- if the database already exists then skip the attach 
         -- print an appropriate message message
         IF (@db2attch_ver > @curr_srv_ver)
            OR
            (exists (SELECT 1 
                     FROM sys.databases d 
                     WHERE RTRIM(LTRIM(lower(d.name))) = RTRIM(LTRIM(lower(@db_name)))))
         BEGIN 
            PRINT ''
            PRINT ' Attach for database ' + @db_name + ' was not performed! '
            PRINT ' Possible reasons : ' 
            PRINT '1. ' +  @db_name + ' DB version is higher that the currnet server version.'
            PRINT '2. ' +  @db_name + ' DB already exists on server.'
            PRINT ''
         END 
         ELSE 
         BEGIN 
            EXEC sp_attach_single_file_db @dbname= @db_name , @physname = @phys_name
            PRINT ''
            PRINT 'Database "' + @db_name + '" attached to server OK using file ' + @currFile + '".'
            PRINT ''
            DELETE FROM @mdfFileATTR
         END 

         FETCH NEXT FROM cf INTO @currFile

      END 
    
      CLOSE cf
      DEALLOCATE cf
   END TRY
   BEGIN CATCH
      PRINT 'Error while attaching FILE ' + @phys_name + ',...Exiting procedure'
      CLOSE cf
      DEALLOCATE cf
   END CATCH 

   SET NOCOUNT OFF
END 
GO

• Cách 2:

DECLARE @DataPath VARCHAR(MAX) = 'Data file path'
DECLARE @LogPath VARCHAR(MAX) = 'Log file path'
 
IF (OBJECT_ID('tempdb.dbo.#DataFiles') IS NOT NULL)
DROP TABLE #DataFiles
CREATE TABLE #DataFiles
(mdf VARCHAR(400))
 
IF (OBJECT_ID('tempdb.dbo.#DBFiles') IS NOT NULL)
DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(status INT,
fileid INT,
name VARCHAR(256),
filename VARCHAR(256))
 
IF (OBJECT_ID('tempdb.dbo.#DBProperties') IS NOT NULL)
DROP TABLE #DBProperties
CREATE TABLE #DBProperties
(property VARCHAR(256),
value SQL_VARIANT)
 
DECLARE @Cmd VARCHAR(4000)
DECLARE @DataFileNames  TABLE (mdfFile nvarchar(260))
DECLARE @DataFileName VARCHAR(256)
DECLARE @ndfFileName VARCHAR(256)
DECLARE @AttachCMD VARCHAR(2000)
 
--get list of .mdf files from data directory
SET @Cmd = 'dir /b "' + @DataPath + '"\*.mdf'
 
INSERT into #DataFiles 
EXEC xp_cmdshell @Cmd
 
--cursor containing only .mdf files are aren't associatated with an existing database
UPDATE #DataFiles
SET mdf = @DataPath + '\' + mdf
DECLARE DataFilesCur CURSOR STATIC FORWARD_ONLY FOR
    SELECT mdf
    FROM #DataFiles
    WHERE mdf != 'null'
    AND mdf NOT IN (SELECT physical_name FROM sys.master_files)
 
OPEN DataFilesCur
 
FETCH NEXT FROM DataFilesCur INTO @DataFileName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    TRUNCATE TABLE #DBFiles
    TRUNCATE TABLE #DBProperties
 
    --DBCC CHECKPRIMARYFILES to return all files associated with the database
    INSERT INTO #DBFiles(status, fileid, name, filename)
    EXEC ('DBCC CHECKPRIMARYFILE(''' + @DataFileName + ''', 3) WITH NO_INFOMSGS')
 
    --DBCC CHECKPRIMARYFILES to get database name
    INSERT INTO #DBProperties (property, value)
    EXEC ('DBCC CHECKPRIMARYFILE(''' + @DataFileName + ''', 2) WITH NO_INFOMSGS')
 
    --Begin constructing file attachment command
    SELECT @AttachCMD =  'CREATE DATABASE ' + QUOTENAME(CAST(value AS SYSNAME))
    FROM #DBProperties
    WHERE property = 'Database name'
 
    SET @AttachCMD = @AttachCMD + ' ON (FILENAME = ''' + @DataFileName + '''),'
 
    SELECT @AttachCMD = @AttachCMD + '(FILENAME = ''' + @LogPath + '\' + REVERSE(SUBSTRING(REVERSE(RTRIM(filename)),0,CHARINDEX('\',REVERSE(RTRIM(filename))))) + ''')'
    FROM #DBFiles
    WHERE filename LIKE '%.ldf'
 
    --cursor through the .ndf files
    DECLARE CURSOR ndfFileCur STATIC FORWARD_ONLY FOR
        SELECT filename
        FROM #DBFiles
        WHERE filename LIKE '%.ndf'
 
    OPEN ndfFileCur
    FETCH NEXT FROM ndfFileCur INTO @ndfFileName
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @AttchCMD = @AttachCMD + ',(FILENAME = ''' + @DataPath + '\' + REVERSE(SUBSTRING(REVERSE(RTRIM(@ndfFileName)),0,CHARINDEX('\',REVERSE(RTRIM(@ndfFileName))))) + ''')'
        FETCH NEXT FROM ndfFileCur INTO @ndfFileName
    END
 
    CLOSE ndfFileCur
    DEALLOCATE ndfFileCur
 
    SET @AttachCMD = @AttachCMD + ' FOR ATTACH'
 
    --PRINT @AttachCMD
    EXEC (@AttachCMD)
    FETCH NEXT FROM DataFilesCur INTO @DataFileName
END
 
CLOSE DataFilesCur
DEALLOCATE DataFilesCur

1. DETACH NHIỀU DATABASE

• Cách 1:

DECLARE @dbName varchar(255); --Biến lưu trữ các database name
DECLARE DBCURSOR CURSOR FOR
    SELECT name
    FROM sys.databases 
    WHERE len(owner_sid)>1; -- All user databases
 
OPEN DBCURSOR
FETCH Next from DBCURSOR INTO @dbName
WHILE @@FETCH_STATUS = 0 
BEGIN
    PRINT 'EXEC sp_detach_db ' + @dbName + CHAR(10) -- CHAR(10) for newline 
    + 'GO' + CHAR(10) 
    + 'Print ''Detach of ' + @dbName + ' database completed successfully'''
    + CHAR(10) + 'GO'
    FETCH NEXT FROM DBCURSOR INTO @dbName
END
 
CLOSE DBCURSOR    
DEALLOCATE DBCURSOR

Cách 2:

WITH Targets
AS (
    SELECT
      dbid,
      DB_NAME(dbid) db_name,
      fileid,
      filename
    FROM master.dbo.sysaltfiles
    WHERE dbid > 4
      AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
   )
SELECT
  db_name,
  'exec sp_detach_db @dbname = N''' + db_name + ''';' Detach,
  'exec sp_attach_db @dbname = N''' + db_name + '''' + (SELECT
    ', @filename' + CAST(fileid AS varchar) + '=N''' + filename + ''''
  FROM Targets f
  WHERE f.dbid = d.dbid
  FOR xml PATH (''))
  + ';' AS Attach
FROM (SELECT DISTINCT dbid, db_name FROM Targets) d

--Copy câu lệnh ra để lấy lệnh thực thi

Cách 3: Áp dụng truyền thêm tham số 1 hoặc 0 để xác định Detach hoặc Attach

USE [master];
GO
DECLARE @database NVARCHAR(200) ,
    @cmd NVARCHAR(1000) ,
    @detach_cmd NVARCHAR(4000) ,
    @attach_cmd NVARCHAR(4000) ,
    @file NVARCHAR(1000) ,
    @i INT ,
    @DetachOrAttach BIT;

SET @DetachOrAttach = 1;

-- 1 Detach 0 - Attach
-- 1 Generates Detach Script
-- 0 Generates Attach Script
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT  RTRIM(LTRIM([name]))
    FROM    sys.databases
    WHERE   database_id > 4;
 -- No system databases
OPEN dbname_cur

FETCH NEXT FROM dbname_cur INTO @database

WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT  @i = 1;

        SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
            + 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
      -- Change skip checks to false if you want to update statistics before you detach.
        SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
            + 'EXEC sp_detach_db @dbname = ''' + @database
            + ''' , @skipchecks = ''true'';' + CHAR(10);

      -- Get a list of files for the database
        DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
        FOR
            SELECT  physical_name
            FROM    sys.master_files
            WHERE   database_id = DB_ID(@database)
            ORDER BY [file_id];

        OPEN dbfiles_cur

        FETCH NEXT FROM dbfiles_cur INTO @file

        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @attach_cmd = @attach_cmd + '    ,@filename'
                    + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
                    + CHAR(10);
                SET @i = @i + 1;

                FETCH NEXT FROM dbfiles_cur INTO @file
            END

        CLOSE dbfiles_cur;

        DEALLOCATE dbfiles_cur;

        IF ( @DetachOrAttach = 0 ) 
            BEGIN
            -- Output attach script
                PRINT @attach_cmd;
            END
        ELSE -- Output detach script
            PRINT @detach_cmd;

        FETCH NEXT FROM dbname_cur INTO @database
    END

CLOSE dbname_cur;

DEALLOCATE dbname_cur;