Script to generate SQL query to detach and attach the database in different drive

Problem:

If the database drive is full and you don't have the option to extend the drive, then you need to add another drive with a greater size. Then detach all databases, copy all your data and log files of database into new drive and attach the database

Lets us do in three steps:

Lets us consider, we have data file under D: drive and log files under E Drive. We need to move it I and J drive.

STEP 1: To list all the databases under the drive D and E

select DISTINCT DB_NAME(dbid) as database_name from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E')

Result:
database_name
master
model
msdb
tempdb
test1
test2
test3

It will list all the databases under the drive D and E

Now generate the query to detach all the database under the drive D and E.Query_1 will generate the query to detach all system tables as well. If you want to skip the system tables, use Query_2

Query_1:

select DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';' from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E')

Query_2:

select DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';' from sys.sysaltfiles

WHERE SUBSTRING(filename,1,1) IN ('D','E')  AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')

Result:

exec sp_detach_db 'test1';
exec sp_detach_db 'test10';
exec sp_detach_db 'test11';
exec sp_detach_db 'test12';
exec sp_detach_db 'test13';

The result for the query will be something like above, exec the above query to detach all the databases.

  
Step 2:

After detaching the databases, Copy all the mdf and ldf files from D,E drives to I and J drives

Step 3:

Generate the sql query to attach all the databases from new drive location:

-- Build the sp_attach_db:
-- In this example, I am assuming that only the drive letter changes except master database, not the whole
-- path of the files. You can modify this script according to your needs:

--It will create a temporary table and delete it after the execution of the command

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 'D' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
                  WHEN 'E' 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 sys.sysaltfiles
            WHERE SUBSTRING(filename,1,1) IN ('D','E'))
            AND DATABASEPROPERTYEX(DB_NAME(dbid) , 'Status' ) = 'ONLINE'
            AND DB_NAME(dbid) NOT IN ('master')
ORDER BY dbname, fileid,
            CASE SUBSTRING(filename,1,1)
                  WHEN 'D' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
                  WHEN 'E' 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

Result:

exec sp_attach_db @dbname = N'model',@filename1=N'I:\MSSQL Server\SQL_PRIMARY\MSSQL13.SQL_PRIMARY\MSSQL\DATA\model.mdf',@filename2=N'I:\MSSQL Server\SQL_PRIMARY\MSSQL13.SQL_PRIMARY\MSSQL\DATA\modellog.ldf'

You can get the result something like above. verify the location and exec the query to attach the database from new data and log drives.

Reference: https://www.mssqltips.com/sqlservertip/1786/auto-generate-sql-server-database-attach-and-detach-scripts/

Thanks,

Sandy.