Creating an ‘Attach all databases’ script

Finally, customers are more and more seeing the importance of a decent test environment before things are put in production. So as a DBA I get  more questions about how to set up those test environments and how to keep them in sync with the production environment. One trick I pulled of lately for a +100 databases environment was the following:

The database files on the production server were on a SAN and lucky me, the files for system and user databases were on separated (Windows) disks. So a test server was installed  (MS SQL Server 2008 R2 while production was MS SQL Server 2000) with the new company defaults to do the upgrade tests. The only thing we needed to care about was not to assign drive letters that were used on the current production environment.

With the basic server setup in place it was time to get the user databases on the server. At this point the SAN-team comes into the picture. They cloned the production disks within seconds and were able to present them  to the test server in the same order as on the production server. Switching back to the test server the newly presented disks were assigned the same drive letters as on production. Finally it was time to get the databases online.

If there were only 5 databases, I could have manually attached them or have generated the attach scripts one by one on the production server. But because I had more than 100 databases to attach it was time to do some scripting.

First I created a temporary table on the production server to gather the file information of the user databases:

 db varchar(50),
 fileid int,
 name varchar (50),
 filename varchar (250)

With the temp table in place I can start gathering the file information from the user databases. Using the sp_MSforeachdb stored procedure I can loop my SELECT statement over every user database:

 EXEC sp_MSforeachdb ‘
  USE ?
  IF ”?” <> ”master” AND ”?” <> ”model” AND ”?” <> ”msdb” AND ”?” <> ”tempdb” AND ”?” <> ”distribution”
    SELECT ”?”, SF.fileid,, SF.filename FROM sysfiles SF

Running trough the gathered data made clear that there were databases with let’s say, quite a history. Files were added and removed over the years so the file_id’s where not consistent any more. Some databases had over 16 files, more than you can attach with ‘sp_attach_db’. But while the production server was Ms SQL Server 2000, the new test server was MS SQL Server 2008 R2. This means that if my database files are all on the same location I only need the location of the primary data file in a ‘CREATE DATABASE … FOR ATTACH’ statement. SQL Server will take care of the rest of the files. Having all the file information already in place in my temp table I can simply run this query to generate a script to attach all the user databases:


SELECT ‘CREATE DATABASE ‘ + db + ‘ ON (FILENAME =  ”’ + RTRIM(filename) + ”’) FOR ATTACH;’
WHERE fileid = 1

Putting the output of above query to Text (CTRL + T) results in a nice script that I can copy and paste to my new test server. Starting the script and waiting for 10 minutes is all I have to do to start testing on the new test environment.

%d bloggers like this: