Auto generate SQL Server database attach and detach scripts

Problem

Often, you have a SQL Server instance containing many databases. It may happen that over time, you have added disk drives or LUNs and placed different database files on separate disk letters. Now you need to know which databases reside on which drive. You may require this because a drive should be replaced and you need to know which databases will be affected or maybe you need to detach all databases from a drive(s) and copy the contents of the entire drive letter to another server (for example, cloning of Production LUNs into a DEV/QA environment).  Another reason may be that you need to map the database locations on the drives for DR purposes, in case you need to rebuild the server from scratch    In this tip we look at how to find what database files reside on what drives as well as the auto generation of attach and detach scripts for your databases.

Solution

In this article I provide three scripts (valid for SQL 2005 and 2008): 

• Script 1 – List of databases for which one or more database files reside on a list of drives 
• Script 2 – The detach commands to detach the databases listed in 1 
• Script 3 – The attach commands to attach back the databases listed in 1, to the same original file locations 

The goal is to find the specific databases, detach them and attach them to the same location.  Depending on what you need to accomplish, you may not need all 3 scripts.

For example, if you want to clone your entire disk and reattach everything back after the cloning, you will use all 3 scripts; if your goal is to move databases from an existing drive to a new one (different letter), you may need only the first two scripts (and you may use the third script as a base, modifying it by replacing old drives with new drives, see the example below after the 3rd script).

In the following examples I am not using a parameter to hold the drive letters, because I didn’t want to use dynamic SQL or create a SP. In my examples, the E: and F: drives need to be replaced and databases need to be detached and reattached to the same location after the replacement. To make this easier to see I have highlighted the drive letters in all scripts, so you can replace this for your particular server.

Script 1 – Create list of database files on the E: and F: drives 

Here is the result set for my server.

dbname
------------------
UserDB1
UserDB5 

Script 2 – Create the detach command for databases that have any files on the E: or F: drives 

Here is the result set for my server.

-------------------------------------------
exec sp_detach_db 'UserDB1';
exec sp_detach_db 'UserDB5'; 

Script 3 – Create the attach command for databases that have any files on the E: or F: drives 

Here is the result set for my server.

------------------------------------------------------------------------------------------------------
exec sp_attach_db @dbname = N'UserDB1',@filename1=N'F:\Databases\UserDB1.mdf',@filename2=N'F:\Databases\UserDB1.ldf'
exec sp_attach_db @dbname = N'UserDB5',@filename1=N'f:\UserDB5.mdf',@filename2=N'C:\UserDB5.ldf',@filename3=N'f:\UserDB5_1.ndf'

Reattaching the database(s) to new drives

Let’s say we need to move the database files as stated below:

• Files on E: moved to I: drive
• Files on F: moved to J: drive

You can use the results of script 3 and replace the old location with the new locations as shown below.

You can also automate the attach command to the new drive by changing Script 3 as follows:

In this example I have highlighted the old values and the new values that you would need to change for your server.

Here is the result set for my server.

------------------------------------------------------------------------------------------------------
exec sp_attach_db @dbname = N'UserDB1',@filename1=N'J:\Databases\UserDB1.mdf',@filename2=N'J:\Databases\UserDB1.ldf'
exec sp_attach_db @dbname = N'UserDB5',@filename1=N'J:\UserDB5.mdf',@filename2=N'C:\UserDB5.ldf',@filename3=N'J:\UserDB5_1.ndf' 

Another Approach Using CTEs

Here is another approach to generate the commands from one of our users.

WITH Targets
AS (
    SELECT
      dbid,
      DB_NAME(dbid) db_name,
      fileid,
      filename
    FROM master.dbo.sysaltfiles
    WHERE dbid > 4
      AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
   )
SELECT
  db_name,
  'exec sp_detach_db @dbname = N''' + db_name + ''';' Detach,
  'exec sp_attach_db @dbname = N''' + db_name + '''' + (SELECT
    ', @filename' + CAST(fileid AS varchar) + '=N''' + filename + ''''
  FROM Targets f
  WHERE f.dbid = d.dbid
  FOR xml PATH (''))
  + ';' AS Attach
FROM (SELECT DISTINCT dbid, db_name FROM Targets) d

Summary

In this article I provided a set of scripts that deal with:

• Locating databases residing on specific disk drives
• Creating the database detach command for databases residing on specific disk drives
• Creating the database attach command for databases residing on specific disk drives

Next Steps

• More information about  How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
• Learn how to move the system databases or take a look at this tip Simplified process to move all SQL Server system databases at one time
• Moving the Resource database
• Don’t forget to plan your Disaster Recovery procedures
• Take these scripts to the next level and allow for parameter passing