Generate scripts to detach/attach all databases

I haven’t had time to do any Denali testing since my last post, so instead I thought I’d share a script I wrote a while ago which simply generates a script to attach one or more databases using their current file paths (and optionally then detaches these databases).

The reason I wrote this script in the first place was because we have a number of environment (i.e. dev, test, stage, reporting, etc), and some of these databases are made up of quite a few database files (i.e. more than a dozen or so).
So on the odd occasion when I’ve needed to detach a database, move some files around, and re-attach said database using the Management Studio GUI, I’ve found it frustrating having to manually browse to each file’s location over and over again. And I just prefer doing stuff in T/SQL over using the GUI – makes me feel more in control I think.

So, with this script I could easily generate the “attach” code for each database (including all file paths), make any path or filename changes necessary, and re-attach all the databases with a single mouse click!

USE [master];

DECLARE @database NVARCHAR(200),
@cmd NVARCHAR(1000),
@attach_cmd NVARCHAR(4000),
@file NVARCHAR(1000),
@i INT;

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  RTRIM(LTRIM([name]))
FROM    sysdatabases
WHERE   [dbid] > 4  -- exclude system databases

OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;

-- Initial attach command stub
SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
+ 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);

-- Get a list of files for this 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;

-- Output "attach" command.
PRINT @attach_cmd;

-- DETACH (uncomment the following line at your peril - IT WILL DETACH ALL USER DATABASES!)
/*EXEC sp_detach_db @dbname = @database, @skipchecks = 'true';  -- change this to false if you want it to update stats before detaching*/

FETCH NEXT FROM dbname_cur INTO @database
END

CLOSE dbname_cur;
DEALLOCATE dbname_cur;

The usual disclaimers apply; I’ve used this in a limited number of scenarios so I wouldn’t consider it thoroughly tested, and if you run it in a production environment and “accidentally” detach databases, or otherwise break something – don’t blame me.

Cheers
Dave