Find all database files for a drive and auto generate database attach and detach scripts for SQL Server
Written By: Michelle Gutzait -- 7/3/2009
-- read/post comments
-- print --
Rating:
   
Rate
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
-- 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
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
-- 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
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
-- 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
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.
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
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' |
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
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|