Auto generate SQL Server database attach and detach scripts

By:   |   Comments (15)   |   Related: 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' 
	

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, November 25, 2022 - 9:25:36 PM - Robin Wilson Back To Top (90714)
Thanks, this was just what I was after and saved having to manually detach and attach nearly 100 databases.

Thursday, October 4, 2018 - 9:34:45 AM - Lisa Back To Top (77843)

Thanks for the help, this is exactly what will save me an hours worth of company time.


Tuesday, June 12, 2018 - 9:36:43 AM - Michel Back To Top (76194)

Hi Michelle (and anybody interested),

Thanks for the article. I changed it a little to have the results in one statement (no cursors ;-).

Cheers,

Michel

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


Monday, August 28, 2017 - 5:23:21 AM - Miroslav Belan Back To Top (65526)

 Hello, I could use your help with something. I need to detach database from disk and attach to completely new one, with different letter. I am not SQL champion so I don't know how to change scripts above to resolve my problem, but I need script as I will do it for couple of systems at the same time.

Just need to change disks, data are copied on the new disk, could you help me please?

 

Thank you in advance,

 

Miroslav Belan.

 


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

 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 (37886)

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 8, 2015 - 4:13:25 AM - Vassilis K Back To Top (37768)

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 9, 2014 - 1:34:36 AM - Greg Thompson Back To Top (35548)

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 7, 2014 - 7:41:36 AM - Subra Back To Top (34038)

Great Script, certainly helped and saved time !!!


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

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 (23297)

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 (23019)

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 (23018)

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 (20554)

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 (20472)

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"















get free sql tips
agree to terms