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 0: ƯU TIÊN SỬ DỤNG

• Verion 2: thêm kiểm tra phân quyền thư mục sql

-- ==============================================================================
-- KỊCH BẢN TỰ ĐỘNG CẤP QUYỀN VÀ ATTACH DATABASE (1 HOẶC TẤT CẢ)
-- Dành cho SQL Server (Hỗ trợ tốt cho Named Instance như DTECH_EXPRESS)
-- ==============================================================================
---------------------------------------------------------------------------------
-- PHẦN 1: CẤU HÌNH THÔNG TIN CHUNG
---------------------------------------------------------------------------------
-- Điền tên DB để attach 1 cái. Để NULL hoặc '' nếu muốn attach TẤT CẢ trong thư mục
DECLARE @database_name NVARCHAR(100) = NULL; 
-- Đường dẫn thư mục chứa file .mdf và .ldf
DECLARE @pathdb NVARCHAR(300) = N'D:\DTECH\DTECH SERVICE\DataSQL'; 

-- Chuẩn hóa đường dẫn (Tự động thêm '\' nếu thiếu)
IF RIGHT(@pathdb, 1) <> '\' SET @pathdb = @pathdb + '\';

---------------------------------------------------------------------------------
-- PHẦN 2: TỰ ĐỘNG CẤP QUYỀN THƯ MỤC TRÁNH LỖI "ACCESS DENIED"
---------------------------------------------------------------------------------
PRINT '----------------------------------------------------------------------';
PRINT N'ĐANG KIỂM TRA VÀ CẤP QUYỀN TRUY CẬP THƯ MỤC...';

-- 2.1 Bật tính năng xp_cmdshell tạm thời
EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;

-- 2.2 Tự động lấy tài khoản Service Account đang chạy SQL Server
DECLARE @ServiceAccount VARCHAR(100);
SELECT TOP 1 @ServiceAccount = service_account 
FROM sys.dm_server_services 
WHERE servicename LIKE 'SQL Server (%';

PRINT N' => Tài khoản SQL Server hiện tại: ' + @ServiceAccount;

-- 2.3 Thực thi lệnh icacls để cấp quyền Full Control cho Service Account
DECLARE @cmd VARCHAR(1000) = 'icacls "' + SUBSTRING(@pathdb, 1, LEN(@pathdb)-1) + '" /grant "' + @ServiceAccount + '":(OI)(CI)F /T';
BEGIN TRY
    EXEC master..xp_cmdshell @cmd, NO_OUTPUT;
    PRINT N' => THÀNH CÔNG: Đã cấp quyền truy cập thư mục hệ thống.';
END TRY
BEGIN CATCH
    PRINT N' => THẤT BẠI: Lỗi khi cấp quyền - ' + ERROR_MESSAGE();
END CATCH

-- 2.4 Tắt tính năng xp_cmdshell để bảo mật hệ thống
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;


---------------------------------------------------------------------------------
-- PHẦN 3: LẤY DANH SÁCH DATABASE CẦN ATTACH
---------------------------------------------------------------------------------
DECLARE @DbToProcess TABLE (DbName NVARCHAR(100), MdfFile NVARCHAR(300));

IF ISNULL(@database_name, '') <> ''
BEGIN
    -- LỰA CHỌN 1: Chỉ xử lý 1 database được chỉ định
    INSERT INTO @DbToProcess (DbName, MdfFile) 
    VALUES (@database_name, @database_name + '.mdf');
END
ELSE
BEGIN
    -- LỰA CHỌN 2: Quét thư mục và lấy tất cả file .mdf
    PRINT '----------------------------------------------------------------------';
    PRINT N'ĐANG QUÉT THƯ MỤC TÌM CÁC FILE .MDF...';
    DECLARE @Files TABLE (FileName NVARCHAR(512), Depth INT, IsFile INT);
    INSERT INTO @Files EXEC master.sys.xp_dirtree @pathdb, 1, 1;

    INSERT INTO @DbToProcess (DbName, MdfFile)
    SELECT 
        REPLACE(FileName, '.mdf', ''), 
        FileName
    FROM @Files
    WHERE IsFile = 1 AND FileName LIKE '%.mdf';
END


---------------------------------------------------------------------------------
-- PHẦN 4: DUYỆT VÀ THỰC THI ATTACH DATABASE
---------------------------------------------------------------------------------
DECLARE @CurrentDb NVARCHAR(100), @CurrentMdf NVARCHAR(300);
DECLARE @sql NVARCHAR(MAX);
DECLARE @Ldf1 NVARCHAR(300), @Ldf2 NVARCHAR(300);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT DbName, MdfFile FROM @DbToProcess;
OPEN cur;
FETCH NEXT FROM cur INTO @CurrentDb, @CurrentMdf;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '----------------------------------------------------------------------';
    PRINT N'Đang xử lý Database: [' + @CurrentDb + ']';

    -- Kiểm tra Database đã tồn tại trên Server chưa (BỎ QUA TRÁNH LỖI)
    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @CurrentDb)
    BEGIN
        PRINT N' => SKIP: Đã tồn tại database [' + @CurrentDb + N'] trên SQL SERVER.';
    END
    ELSE
    BEGIN
        -- Chuẩn bị 2 kịch bản tên file log
        SET @Ldf1 = @pathdb + @CurrentDb + '_log.ldf';
        SET @Ldf2 = @pathdb + @CurrentDb + '.ldf';

        BEGIN TRY
            -- Thử kịch bản 1: File log có hậu tố _log.ldf
            SET @sql = N'CREATE DATABASE [' + @CurrentDb + N'] ON 
                (FILENAME = N''' + @pathdb + @CurrentMdf + N'''), 
                (FILENAME = N''' + @Ldf1 + N''') FOR ATTACH;';
            EXEC sp_executesql @sql;
            PRINT N' => THÀNH CÔNG: Đã attach bằng file _log.ldf';
        END TRY
        BEGIN CATCH
            -- Nếu kịch bản 1 lỗi, lập tức thử kịch bản 2: File log chỉ có hậu tố .ldf
            BEGIN TRY
                SET @sql = N'CREATE DATABASE [' + @CurrentDb + N'] ON 
                    (FILENAME = N''' + @pathdb + @CurrentMdf + N'''), 
                    (FILENAME = N''' + @Ldf2 + N''') FOR ATTACH;';
                EXEC sp_executesql @sql;
                PRINT N' => THÀNH CÔNG: Đã attach bằng file .ldf';
            END TRY
            BEGIN CATCH
                PRINT N' => THẤT BẠI: Lỗi khi Attach - ' + ERROR_MESSAGE();
            END CATCH
        END CATCH
    END

    FETCH NEXT FROM cur INTO @CurrentDb, @CurrentMdf;
END

CLOSE cur;
DEALLOCATE cur;

PRINT '----------------------------------------------------------------------';
PRINT N'HOÀN TẤT QUÁ TRÌNH!';
PRINT '----------------------------------------------------------------------';

-- Hiển thị kết quả danh sách DB hiện tại để kiểm tra
SELECT name, state_desc AS [Trạng Thái], recovery_model_desc AS [Recovery Model] 
FROM sys.databases
WHERE database_id > 4; -- Bỏ qua các DB hệ thống (master, tempdb, model, msdb)

• Verion 1: chưa có kiểm tra phân quyền

-- ==============================================================================
-- KỊCH BẢN ATTACH DATABASE TỰ ĐỘNG (1 HOẶC TẤT CẢ)
-- ==============================================================================

DECLARE @database_name NVARCHAR(100) = NULL; -- [CẤU HÌNH]: Điền tên DB để attach 1 cái. Để NULL hoặc '' nếu muốn attach TẤT CẢ trong thư mục.
DECLARE @pathdb NVARCHAR(300) = N'D:\DTECH\DTECH SERVICE\DataSQL'; -- [CẤU HÌNH]: Thư mục chứa file

-- 1. Chuẩn hóa đường dẫn (Tự động thêm '\' nếu thiếu)
IF RIGHT(@pathdb, 1) <> '\' SET @pathdb = @pathdb + '\';

-- 2. Bảng tạm chứa danh sách các Database cần xử lý
DECLARE @DbToProcess TABLE (DbName NVARCHAR(100), MdfFile NVARCHAR(300));

IF ISNULL(@database_name, '') <> ''
BEGIN
    -- LỰA CHỌN 1: Chỉ xử lý 1 database được chỉ định
    INSERT INTO @DbToProcess (DbName, MdfFile) 
    VALUES (@database_name, @database_name + '.mdf');
END
ELSE
BEGIN
    -- LỰA CHỌN 2: Đọc thư mục Windows và lấy tất cả file .mdf
    -- Sử dụng xp_dirtree để lấy danh sách file một cách an toàn
    DECLARE @Files TABLE (FileName NVARCHAR(512), Depth INT, IsFile INT);
    INSERT INTO @Files EXEC master.sys.xp_dirtree @pathdb, 1, 1;

    INSERT INTO @DbToProcess (DbName, MdfFile)
    SELECT 
        REPLACE(FileName, '.mdf', ''), -- Cắt đuôi .mdf để lấy tên DB
        FileName
    FROM @Files
    WHERE IsFile = 1 AND FileName LIKE '%.mdf';
END

-- 3. Xử lý logic Attach (Dùng Cursor để duyệt qua danh sách)
DECLARE @CurrentDb NVARCHAR(100), @CurrentMdf NVARCHAR(300);
DECLARE @sql NVARCHAR(MAX);
DECLARE @Ldf1 NVARCHAR(300), @Ldf2 NVARCHAR(300);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT DbName, MdfFile FROM @DbToProcess;
OPEN cur;
FETCH NEXT FROM cur INTO @CurrentDb, @CurrentMdf;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '----------------------------------------------------------------------';
    PRINT N'Đang kiểm tra và xử lý Database: [' + @CurrentDb + ']';

    -- Kiểm tra Database đã tồn tại trên Server chưa
    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @CurrentDb)
    BEGIN
        PRINT N' => BỎ QUA: Đã tồn tại database [' + @CurrentDb + N'] trên SQL SERVER.';
    END
    ELSE
    BEGIN
        -- Chuẩn bị 2 kịch bản tên file log như logic gốc của bạn
        SET @Ldf1 = @pathdb + @CurrentDb + '_log.ldf';
        SET @Ldf2 = @pathdb + @CurrentDb + '.ldf';

        BEGIN TRY
            -- Thử kịch bản 1: File log có hậu tố _log.ldf
            SET @sql = N'CREATE DATABASE [' + @CurrentDb + N'] ON 
                (FILENAME = N''' + @pathdb + @CurrentMdf + N'''), 
                (FILENAME = N''' + @Ldf1 + N''') FOR ATTACH;';
            EXEC sp_executesql @sql;
            PRINT N' => THÀNH CÔNG: Đã attach bằng file _log.ldf';
        END TRY
        BEGIN CATCH
            -- Nếu kịch bản 1 lỗi, lập tức thử kịch bản 2: File log chỉ có hậu tố .ldf
            BEGIN TRY
                SET @sql = N'CREATE DATABASE [' + @CurrentDb + N'] ON 
                    (FILENAME = N''' + @pathdb + @CurrentMdf + N'''), 
                    (FILENAME = N''' + @Ldf2 + N''') FOR ATTACH;';
                EXEC sp_executesql @sql;
                PRINT N' => THÀNH CÔNG: Đã attach bằng file .ldf';
            END TRY
            BEGIN CATCH
                -- Nếu cả 2 đều thất bại, in ra lỗi chi tiết
                PRINT N' => THẤT BẠI: Lỗi hệ thống: ' + ERROR_MESSAGE();
            END CATCH
        END CATCH
    END

    FETCH NEXT FROM cur INTO @CurrentDb, @CurrentMdf;
END

CLOSE cur;
DEALLOCATE cur;

PRINT '----------------------------------------------------------------------';
PRINT N'HOÀN TẤT QUÁ TRÌNH!';

-- Xem lại danh sách Database
SELECT name, state_desc, recovery_model_desc FROM sys.databases;

• 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;

PHÂN QUYỀN THƯ MỤC DATASQL

-- Code phân quyền thư mục Data SQL
-- Lệnh cmd ví dụ: ICACLS "D:\DTECH\DTECH88 Service\DataSQL" /grant "Everyone":F /T
-- 2. Chuẩn bị lệnh icacls và tự động dò tìm Service Account
DECLARE @cmd VARCHAR(1000);
DECLARE @folderPath VARCHAR(300) = 'D:\DTECH\DTECH SERVICE\DataSQL';
DECLARE @ServiceAccount VARCHAR(100);

-- Tự động lấy tài khoản đang chạy dịch vụ SQL Server hiện tại (Engine)
SELECT TOP 1 @ServiceAccount = service_account 
FROM sys.dm_server_services 
WHERE servicename LIKE 'SQL Server (%';

PRINT N'Tài khoản SQL Server đang sử dụng là: ' + @ServiceAccount;

-- Chuẩn bị lệnh CMD cấp quyền
SET @cmd = 'icacls "' + @folderPath + '" /grant "' + @ServiceAccount + '":(OI)(CI)F /T';
PRINT N'Thực thi lệnh CMD: ' + @cmd;