Aşağıdaki sp ile mevcut bir ortamda olan db lerin attach scriptlerini generate edeceğiz, disk kopyalamada, disk klonlama işlemlerinde hızlıca database’leri attach etmek için lazım olabilir. Hedef sunucu da disk adresleri değişmişse scriptin çıktısını değiştirmeniz gerekebilir, ama aynı harflerle diskler mount edilmişse direk çalıştırılabilir.

----Tüm databaseleri detach etmek için script
DECLARE @dbName varchar(255);
DECLARE DBCURSOR CURSOR LOCAL FOR
SELECT name
FROM sys.databases 
WHERE database_id>4; 
---- System dbleri hariç sadece user databaseleri
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) 
+'--- '+ @dbName + ' databasei için detach scripti oluşturuldu.''' 
+ CHAR(10) + 'GO'
FETCH NEXT FROM DBCURSOR INTO @dbName
END

Aşağıdaki gibi bir çıktı verecektir..
EXEC sp_detach_db omer
GO
--- omer databasei için detach scripti olusturuldu.'
GO
EXEC sp_detach_db omer_archive
GO
--- omer_archive databasei için detach scripti olusturuldu.'
GO
EXEC sp_detach_db LoginInfoDB
GO
--- LoginInfoDB databasei için detach scripti olusturuldu.'
GO

----Tüm databaseleri attach etmek için script üreten stored procedure
use master
GO

IF EXISTS (SELECT name
FROM sys.sysobjects
WHERE name = 'SpGenerateAttachScript')
DROP PROCEDURE SpGenerateAttachScript
GO

CREATE PROC SpGenerateAttachScript
@db SYSNAME = NULL
AS
SET nocount ON
SET concat_null_yields_null OFF
DECLARE @cmd VARCHAR(1000),
@a VARCHAR(MAX),
@Filecnt INT,
@cnt INT,
@fileid INT,
@sq CHAR(1),
@dq CHAR(2),
@TempFilename VARCHAR(1000),
@TempFilename1 VARCHAR(1000)
SET @sq = ''''
SET @dq = ''''''
SET @cnt = 1
SET @fileid = 1
IF @db IS NOT NULL
BEGIN
CREATE TABLE #1 (
fileid INT,
filename SYSNAME,
name SYSNAME)
SET @cmd = 'Insert into #1 (fileid,filename,name) select file_id, physical_name, name 
from sys.master_files where database_id = db_id('''
+ @db
+ ''')'
EXEC( @cmd)
SELECT @filecnt = (SELECT COUNT(* )
FROM #1)
WHILE @cnt <= @filecnt
BEGIN
SELECT @TempFileName = filename
FROM #1
WHERE fileid = @fileid
SELECT @TempFileName = RTRIM(@TempFileName)
SELECT @a = @a
+ CHAR(13)
+ CHAR(9)
SELECT @a = @a
+ '(Filename = '
+ @sq
+ @TempFilename
+ @sq
+ '),'
SET @cnt = @cnt
+ 1
SET @fileid = (SELECT MIN(fileid)
FROM #1
WHERE fileid > @fileid)
END
SELECT @a = 'CREATE DATABASE '
+ @db
+ ' ON '
+ @a
SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1)) --Trim off the final comma; I'm sure there is a better way to do this... 
SELECT @a = @a
+ '
FOR ATTACH;
GO'
PRINT @a
END
ELSE
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('tempdb','master','msdb','model')
AND database_id NOT IN (SELECT database_id
FROM sys.databases
WHERE state > 0)
ORDER BY database_id
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #2 (
fileid INT,
filename SYSNAME,
name SYSNAME)
SET @cmd = 'Insert into #2 (fileid,filename,name) select file_id, physical_name, name 
from sys.master_files where database_id = db_id('''
+ @db
+ ''')'
EXEC( @cmd)
SELECT @filecnt = (SELECT COUNT(* )
FROM #2)
WHILE @cnt <= @filecnt
BEGIN
SELECT @TempFileName = filename
FROM #2
WHERE fileid = @fileid
SELECT @TempFileName = RTRIM(@TempFileName)
SELECT @a = @a
+ CHAR(13)
+ CHAR(9)
SELECT @a = @a
+ '(Filename = '
+ @sq
+ @TempFilename
+ @sq
+ '),'
SET @cnt = @cnt
+ 1
SET @fileid = (SELECT MIN(fileid)
FROM #2
WHERE fileid > @fileid)
END
SELECT @a = 'CREATE DATABASE '
+ @db
+ ' ON '
+ @a
SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1))
SELECT @a = @a
+ '
FOR ATTACH;
GO

'
PRINT @a
SELECT @a = ' '
DROP TABLE #2
SET @cnt = 1
SET @fileid = 1
FETCH NEXT FROM db_cursor
INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Sp yi create ettikten sonra çalıştırıyoruz.
exec SpGenerateAttachScript

Aşağıdaki gibi bir çıktı vermelidir.
CREATE DATABASE omer ON
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\omer.mdf'),
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\omer_log.ldf')
FOR ATTACH;
GO

CREATE DATABASE LoginInfoDB ON
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\LoginInfoDB.mdf'),
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\LoginInfoDB_log.ldf')
FOR ATTACH;
GO