By: Michelle Gutzait | Updated: 2020-01-06 | 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
- 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
About the author
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
Article Last Updated: 2020-01-06
