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