Dettach and Attach Databases Script
Hello Guys,
I was looking for some simple and fast way to generate a list for dettach and attach databases, but the most of scripts on the internet uses cursor and as i really hate cursors, i decided to write my own scripts, because it is a good way to learn and improve your T-SQL Skills.
So, i will put the code for two scripts below and you could download these scripts here
Dettach Databases
— If you put 1 the databases will be dettached
SET nocount ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DatabaseName NVARCHAR(512)
DECLARE @ExecuteNow BIT
SET @ExecuteNow = 0
DECLARE @table TABLE
(
id INT ,
dbName NVARCHAR(512) ,
isVerified BIT
)
INSERT INTO @table
SELECT database_id ,
name ,
0 AS isVerified
FROM sys.databases
WHERE name NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’,
‘ReportServerTempDB’,
‘ReportServer’, ‘distribution’ )
ORDER BY database_id
WHILE ( SELECT COUNT(*)
FROM @table
WHERE isVerified = 0
) > 0
BEGIN
SELECT TOP ( 1 )
@DatabaseName = dbName
FROM @table
WHERE isVerified = 0
IF @ExecuteNow = 1
BEGIN
SET @SQL = ‘exec sp_detach_db ”’ + @databaseName + ””
EXEC sp_executesql @sql
END
PRINT ‘exec sp_detach_db ”[‘ + @databaseName + ‘]”’
UPDATE @table
SET isVerified = 1
WHERE @DatabaseName = dbName
END
SET nocount OFF
And now how to attach databases
SET nocount ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DatabaseName NVARCHAR(512)
DECLARE @table TABLE
(
id INT ,
dbName NVARCHAR(512) ,
isVerified BIT
)
INSERT INTO @table
SELECT database_id ,
name ,
0 AS isVerified
FROM sys.databases
WHERE name NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’,
‘ReportServerTempDB’,
‘ReportServer’, ‘distribution’ )
ORDER BY database_id
WHILE ( SELECT COUNT(*)
FROM @table
WHERE isVerified = 0
) > 0
BEGIN
SELECT TOP ( 1 )
@DatabaseName = dbName
FROM @table
WHERE isVerified = 0
SET @SQL = ( SELECT TOP ( 1 )
‘exec sp_attach_db ”’ + @DatabaseName + ”’ , ‘
+ ( SELECT ”” + physical_name + ””
FROM sys.master_files
WHERE database_id = DB_ID(@DatabaseName)
AND RIGHT(physical_name, 3) = ‘mdf’
) + ‘ , ‘
+ ( SELECT ”” + physical_name + ””
FROM sys.master_files
WHERE database_id = DB_ID(@DatabaseName)
AND RIGHT(physical_name, 3) = ‘ldf’
)
FROM @table
)
PRINT @sql
UPDATE @table
SET isVerified = 1
WHERE @DatabaseName = dbName
END
SET nocount OF
So, that’s a simple and good way to generate attach and dettach, it isn’t the best, but can help you.
Regards,
Marcos Freccia
Posted on September 8, 2011, in Desenvolvimento, Scripts, VirtualPass and tagged Script to Attach Databases, Script to Dettach Database, SQL Server 2008. Bookmark the permalink. 1 Comment.
Nice article man… but, I don´t think that there are many situations in wich you will need to detach all of your databases in one instance, and the attach in another instance…
In cases like migration, or version Upgrade, I really preffer (my opinion), Full backup databases previously, and than take a tail log backup…
By the way…
Nice article…
Congrats.