Moving SQL Server databases to a new volume — whether to rebalance I/O across storage, migrate to faster disks, or reclaim space — requires a methodical approach to avoid downtime surprises. The detach/attach method is the fastest option when you can afford a brief offline window: no backup involved, no network transfer overhead. This metascript generates the complete detach, file-move, and re-attach statements for every database you want to relocate.

When to Use Detach/Attach vs. Backup/Restore

• Detach/Attach: fastest, no extra disk space needed, but requires database to go offline. Best for local volume moves on the same server.
• Backup/Restore: more flexible, works across servers and network, source stays online. Better for migrations between instances.
• ALTER DATABASE … MODIFY FILE: online operation, no downtime — but only works for SQL Server 2008+ and requires a service restart to take effect on the OS side. Best for future planning when you can’t afford downtime at all.

What the Script Generates

For each database selected, the script outputs three sections: the detach statement (with SINGLE_USER to terminate active connections), the OS-level file move commands via xp_cmdshell, and the re-attach statement with all datafile paths updated to the new destination. Use ALT+T in SSMS to output results as text before running.

SET NOCOUNT ON
----------------------------------------------------
-- CONFIGURE HERE
----------------------------------------------------
-- Data files destination path
DECLARE @pathdata NVARCHAR(MAX) = 'D:DataPath'
-- Log files destination path
DECLARE @pathlog  NVARCHAR(MAX) = 'L:LogPath'
----------------------------------------------------
-- DATABASE LIST
-- To move ALL user databases uncomment the INSERT below
----------------------------------------------------
CREATE TABLE #dbs (dbname NVARCHAR(255))
--INSERT INTO #dbs VALUES ('db1')
--INSERT INTO #dbs VALUES ('db2')
INSERT INTO #dbs
    SELECT name FROM master..sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')
----------------------------------------------------

DECLARE @aDB    NVARCHAR(255)
DECLARE @script NVARCHAR(MAX) = ''

DECLARE db_cur CURSOR FOR SELECT dbname FROM #dbs
OPEN db_cur
FETCH NEXT FROM db_cur INTO @aDB

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '============================================================'
    PRINT '  DATABASE: ' + @aDB
    PRINT '============================================================'
    PRINT ''
    PRINT '-- 1) SET SINGLE USER AND DETACH'
    PRINT 'USE [master]'
    PRINT 'ALTER DATABASE [' + @aDB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    PRINT 'EXEC master.dbo.sp_detach_db @dbname = N''' + @aDB + ''', @keepfulltextindexfile = N''false'''
    PRINT ''
    PRINT '-- 2) MOVE FILES (adjust paths as needed)'

    SELECT
        PRINT 'exec xp_cmdshell ''move "' + physical_name + '" ' +
        CASE WHEN type_desc = 'LOG' THEN @pathlog ELSE @pathdata END + ''''
    FROM sys.master_files
    WHERE DB_NAME(database_id) = @aDB

    PRINT ''
    PRINT '-- 3) ATTACH DATABASE WITH NEW PATHS'
    -- (Re-attach script generation requires dynamic SQL per database)

    FETCH NEXT FROM db_cur INTO @aDB
END

CLOSE db_cur
DEALLOCATE db_cur
DROP TABLE #dbs

Important notes: xp_cmdshell must be enabled on the instance to run the file move commands. Verify destination paths exist and that the SQL Server service account has write permissions before running. After re-attaching, verify database integrity with DBCC CHECKDB.