Find all database files for a drive and auto generate database attach and detach scripts for SQL Server

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):

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.

-- List all databases attached to the drives regardless of the database status:  

SELECT DISTINCT DB_NAME(dbid) 
FROM master.dbo.sysaltfiles 
WHERE SUBSTRING(filename,1,1) IN ('E','F') 
GO 

-- List all ONLINE databases: 
SELECT DISTINCT DB_NAME(dbid) 
FROM master.dbo.sysaltfiles 
WHERE SUBSTRING(filename,1,1) IN ('E','F') 
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
GO 

-- Alert if there is any system database on the specific drives: 
IF EXISTS (SELECT 1 
FROM master.dbo.sysaltfiles 
WHERE SUBSTRING(filename,1,1) IN ('E','F') 
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
AND DB_NAME(dbid) IN ('master','tempdb','msdb','model')  
) 
BEGIN 
  SELECT DISTINCT DB_NAME(dbid) AS 'There are system databases on these drives:' 
  FROM master.dbo.sysaltfiles 
  WHERE SUBSTRING(filename,1,1) IN ('E','F') 
  AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
  AND DB_NAME(dbid) IN ('master','tempdb','msdb','model') 
END 
GO 

-- List all ONLINE databases attached to the drives, except for system databases: 
SELECT DISTINCT DB_NAME(dbid) 
FROM master.dbo.sysaltfiles 
WHERE SUBSTRING(filename,1,1) IN ('E','F') 
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model') 
GO

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

-- Build the sp_detach_db command (ONLINE, non-system databases only): 
SELECT DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';' 
FROM master.dbo.sysaltfiles 
WHERE SUBSTRING(filename,1,1) IN ('E','F') 
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model') 
GO

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

-- Build the sp_attach_db: 
-- (I preach everyone against using cursor... so I don't) 
SET NOCOUNT ON  
DECLARE     @cmd        VARCHAR(MAX), 
            @dbname     VARCHAR(200), 
            @prevdbname VARCHAR(200) 

SELECT @cmd = '', @dbname = ';', @prevdbname = '' 

CREATE TABLE #Attach 
    (Seq        INT IDENTITY(1,1) PRIMARY KEY, 
     dbname     SYSNAME NULL, 
     fileid     INT NULL, 
     filename   VARCHAR(1000) NULL, 
     TxtAttach  VARCHAR(MAX) NULL 
) 

INSERT INTO #Attach 
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach 
FROM master.dbo.sysaltfiles 
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles  
            WHERE SUBSTRING(filename,1,1) IN ('E','F')) 
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model') 
ORDER BY dbname, fileid, filename 

UPDATE #Attach 
SET @cmd = TxtAttach =   
            CASE WHEN dbname <> @prevdbname  
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''') 
            ELSE @cmd 
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''', 
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END, 
    @dbname = dbname 
FROM #Attach  WITH (INDEX(0),TABLOCKX) 
 OPTION (MAXDOP 1) 

SELECT TxtAttach 
FROM 
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach  
 GROUP BY dbname) AS x 

DROP TABLE #Attach 
GO

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

EXEC sp_attach_db @dbname = N'UserDB1', 
@filename1=N'J:\Databases\UserDB1.mdf', -- It was F: before 
@filename2=N'J:\Databases\UserDB1.ldf' -- It was F: before 

EXEC sp_attach_db @dbname = N'UserDB5',@filename1=N'J:\UserDB5.mdf', -- It was F: before 
@filename2=N'C:\UserDB5.ldf',  
@filename3=N'J:\UserDB5_1.ndf' -- It was F: before 
GO

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.

-- Build the sp_attach_db: 
-- In this example, I am assuming that only the drive letter changes, not the whole 
-- path of the files. You can modify this script according to your needs: 
SET NOCOUNT ON  
DECLARE     @cmd        VARCHAR(MAX), 
            @dbname     VARCHAR(200), 
            @prevdbname VARCHAR(200) 

SELECT @cmd = '', @dbname = ';', @prevdbname = '' 
CREATE TABLE #Attach 
    (Seq        INT IDENTITY(1,1) PRIMARY KEY, 
     dbname     SYSNAME       NULL, 
     fileid     INT           NULL, 
     filename   VARCHAR(1000) NULL, 
     TxtAttach  VARCHAR( MAX)  NULL 
) 

INSERT INTO #Attach 
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid,  
            CASE SUBSTRING(filename,1,1)  
                  WHEN 'E' THEN 'I' + SUBSTRING(filename,2,LEN(filename))  
                  WHEN 'F' THEN 'J' + SUBSTRING(filename,2,LEN(filename)) 
                  ELSE filename 
            END, 
            CONVERT(VARCHAR(MAX),'') AS TxtAttach 
FROM master.dbo.sysaltfiles 
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles  
            WHERE SUBSTRING(filename,1,1) IN ('E','F')) 
            AND DATABASEPROPERTYEX(DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model') 
ORDER BY dbname, fileid,  
            CASE SUBSTRING(filename,1,1)  
                  WHEN 'E' THEN 'I' + SUBSTRING(filename,2,LEN(filename))  
                  WHEN 'F' THEN 'J' + SUBSTRING(filename,2,LEN(filename)) 
                  ELSE filename 
            END 

UPDATE #Attach 
SET @cmd = TxtAttach =   
            CASE WHEN dbname <> @prevdbname  
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''') 
            ELSE @cmd 
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''', 
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END, 
    @dbname = dbname 
FROM #Attach  WITH (INDEX(0),TABLOCKX) 
OPTION (MAXDOP 1) 

SELECT TxtAttach 
FROM 
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach 
GROUP BY dbname) AS x 

DROP TABLE #Attach 
GO