Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Auto generate SQL Server database attach and detach scripts


By:   |   Read Comments (11)   |   Related Tips: More > Database Administration

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



Last Update:






About the author
MSSQLTips author Michelle Gutzait Michelle Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, February 12, 2016 - 5:41:47 AM - Ashish Kesarkar Back To Top

 Hey,

This will save lot of our Manual work in SQL Server 2008 to 2014 Migration.

Thank you very much.

- Ashish Kesarkar

 


Wednesday, June 10, 2015 - 11:49:56 AM - James E. Satterthwaite Back To Top

Thanks Michelle,

This saved me greatly: I remember Microsoft long ago had 2 versions of this code, but I have lost track of it...

I ended up altering the insert for it to work on my 2012 instance:

- we don't use the drive letters given and only fileID 1 indicates a status [2 and beyond are NULL]

the altered insert:

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 FileID = 1
            AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model'))
ORDER BY DB_NAME(dbid), fileid, filename

Thanks again!!!

 

 

 

 


Monday, June 08, 2015 - 4:13:25 AM - Vassilis K Back To Top

Direct and trully usefull article from a real professional. Thank you. (Today I have to detach and reattach about 1000 db's (998 db's to be accurate))


Tuesday, December 09, 2014 - 1:34:36 AM - Greg Thompson Back To Top

Just want to pass on a big thumbs up for this very informative article. We are presently migrating a number of SharePoint 2010 databases from SQL 2008 R2 to SQL 2012. Some of these databases have over a 100 seperate data files. i was previously using a script to generate the attach commands but this script was using cursor and failed to do the job. Excellent article!


Thursday, August 07, 2014 - 7:41:36 AM - Subra Back To Top

Great Script, certainly helped and saved time !!!


Wednesday, November 13, 2013 - 4:11:03 PM - Brian Back To Top

Great script - is there any way it can be modified to pick up already detached data and log files in specific drives and then generate the relevant attach command


Thursday, April 11, 2013 - 2:05:33 AM - Crudeli Eric Back To Top

Hello Michelle,

I used your script to attach some databases after moving them on another Drive but I got some errors on some databases and I don't understand where is the issue. Could you help me ?

exec 

sp_attach_db@dbname=N'DMC_TRACE_EXP',@filename1=N'Q:\CNET\EXP\DATA\DMC_TRACE_EXP.mdf',@filename2=

N'H:\CNET\EXP\log\DMC_TRACE_EXP_1.ldf'

 

 

FCB::Open: Operating system error 2(error not found) occurred while creating or opening file 'H:\CNET\EXP\log\DMC_TRACE_EXP_1.ldf'. Diagnose and correct the operating system error, and retry the operation.

And when I tried the following script it's work :

USE [master]

GO

CREATE

DATABASE[DMC_TRACE_EXP]ON

(

FILENAME=N'Q:\CNET\EXP\DATA\DMC_TRACE_EXP.mdf'

),

(

FILENAME=N'H:\CNET\EXP\log\DMC_TRACE_EXP.ldf'

)

FORATTACH

GO

 

 

 


Tuesday, March 26, 2013 - 10:34:28 AM - Greg Robidoux Back To Top

Hi Ed, you need to move the system databases differenlty.

Take a look at this tip: http://www.mssqltips.com/sqlservertip/1604/move-all-sql-server-system-databases-at-one-time/


Tuesday, March 26, 2013 - 10:18:44 AM - Ed Middlebrooks Back To Top

Thanks for this.  Your code though excludes the system databases.  I need to move all databases to a new drive including the system ones.  Is there a reason you chose to exclude these?


Monday, November 26, 2012 - 9:32:32 PM - Michelle Back To Top

Thanks for your comment, I will review it and see if we can modify the original article.

 

Michelle.


Thursday, November 22, 2012 - 11:50:25 AM - Matthew Ogden Back To Top

thanks, certainly faster using your script than mkaing my own, and yours is so very concise.

In script 3 (and script 4/5 which is similiar) there is a bracket missing at line 22, column 72. TO close off the sub query of where DBID is "IN"


Learn more about SQL Server tools