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

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em setembro 8, 2011, em Desenvolvimento, Scripts, VirtualPass e marcado como , , . Adicione o link aos favoritos. 1 comentário.

  1. 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.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: