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;
GOLệ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)
GO2. 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'
GO3. ATTACH , DETACH NHIỀU DATABASES
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
GOCá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 DataFilesCurDETACH 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 DBCURSORCá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 thiCá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;Last updated