Script for Quickly Moving All Database Files on a SQL Instance

Sometimes there’s a need to move the underlying files for the databases on a SQL instance. 

Scenario

You have all your data files on the “E drive” which is really a RAID.  It has 500 Gbytes available which is plenty.  Months later, the E drive becomes full.  You request more disk.  Some time later your storage engineer gives you a new dedicated  RAID which has been assigned a new drive letter, “S “.  This S volume now has 2 Terabytes of space.

The task at hand is to move some of the databases from the E drive to the S drive.  This is a production server so you want to minimize downtime. 

Strategy

1.      Verify original file locations.

2.       Make a database backup for safety.

3.       Kill any active spids.

4.       Detach files.

5.       Copy files to new location.

6.       Attach files.

7.       Verify database is backed up.

8.       Delete files in original location.

This proc will create T-SQL and some command code for doing all the above.

To create calls for all databases.

SELECT 'Exec MoveDB ''' + name + ''', ''S:\Sql\Data'', ''S:\Sql\Data'', ''S:\Sql\Bak'''

FROM sys.databases           

ORDER BY name          

I put his proc in the Admin database with my other code snippets.

USE Admin

If OBJECT_ID('dbo.MoveDB') Is Not Null

      DROP PROC dbo.MoveDB

go

CREATE PROC [dbo].[MoveDB]

      @DB varchar(100),

      @DestDataPath varchar(300),

      @DestLogPath varchar(300),

      @BackupPath varchar(300) = 'BackupPathHere'

AS

SET NOCOUNT ON

/*    DATE        AUTHOR            REMARKS

      11/1/14           PPaiva            Initial creation.

     

      DESCRIPTION

            Produces scripts for moving a database's files via

                  sp_detach and CREATE DATABASE FOR ATTACH.

            Also produces clean-up scripts for deleting

                  the original files, for use after database

                  has been successfully moved.

                 

      USAGE

            MoveDB 'Admin', 'E:\Sql\Data', 'F:\Sql\Logs'

            MoveDB 'Sopa', 'E:\Sql\Data', 'F:\Sql\Logs'

                 

      DEBUG

            -- Location of database files

            SELECT DB_NAME(database_id), file_id, type_desc, physical_name, database_id

            FROM master.sys.master_files

            ORDER BY DB_NAME(database_id), file_id

           

            -- Generate Exec scripts for all databases

            SELECT 'Exec MoveDB ''' + name + ''', ''S:\Sql\Data'', ''S:\Sql\Data'', ''S:\Sql\Bak'''

            FROM sys.databases           

            ORDER BY name          

     

*/

DECLARE @s varchar(max),

            @sAttach varchar(max) = '',

            @sCmdLine varchar(max) = '',

            @sCmdLineDel varchar(max) = '',

            @sTSql varchar(max) = '',

            @sTSqlDel varchar(max) = '',

            @crlf varchar(2) = Char(13) + Char(10)

SET @s = '-- *******************************************

--  Script for moving database ' + @DB + '

-- *******************************************'

Print @s

Print ''

SET @s = 'SELECT file_id, type_desc, physical_name, *

FROM master.sys.master_files

WHERE DB_NAME(database_id) = ''' + @DB + ''''

Print '-- Verify original file locations'

Print @s

Print ''

Print '-- Make a backup for safety'

SET @s = 'Exec Admin.dbo.BackupDB ''' + @DB + ''', ''' + @BackupPath + ''', ''DT'', 1'

Print @s

Print ''

-- Kill any active connections

Print '-- Kill any active spids'

SET @s = 'ALTER DATABASE [' + @DB + ']

      SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [' + @DB + ']

      SET ONLINE'

Print @s   

Print ''

Print '-- Detach'

SET @s = 'Exec master.dbo.sp_detach_db @dbname = [' + @DB + ']'

Print @s

Print ''

-- Copy files

SELECT file_id, type_desc, physical_name

INTO #Files

FROM master.sys.master_files

WHERE DB_NAME(database_id) = @DB

DECLARE @MaxFileID int,

            @FileID int = 0,

            @ExistingPath varchar(300),

            @Usage varchar(20),

            @FileNameOnly varchar(100),

            @NumFiles int,

            @i int = 0

SELECT  @MaxFileID = MAX(file_id),

            @NumFiles = COUNT(*)

FROM #Files

SET @sAttach = 'CREATE DATABASE [' + @DB + '] ON '

WHILE @FileID < @MaxFileID

      BEGIN

            SET @i += 1

           

            -- Get next file

            SELECT @FileID = Min(FILE_ID)

            FROM #Files

            WHERE FILE_ID > @FileID

           

            -- Get other attributes

            SELECT  @ExistingPath = physical_name,

                        @Usage = type_desc,

                        @FileNameOnly = dbo.udfGetFilenameAndExtOnly(physical_name)

            FROM #Files

            WHERE FILE_ID = @FileID

           

           

     

            SET @sCmdLine += 'Copy "' + @ExistingPath + '" "'

            SET @sTSql += 'Exec xp_cmdshell ''Copy "' + @ExistingPath + '" "'

            SET @sCmdLineDel += 'Del "' + @ExistingPath  + '"'         

            SET @sTSqlDel += 'Exec xp_cmdshell ''Del "' + @ExistingPath + '"'''          

           

            IF @Usage = 'Rows'

                  BEGIN

                        SET @sCmdLine += @DestDataPath + '"'                       

                        SET @sTSql += @DestDataPath + '"'''                  

                  END

            ELSE

                  BEGIN

                        SET @sCmdLine += @DestLogPath + '"'

                        SET @sTSql += @DestLogPath + '"'''

                  END

     

            SET @sCmdLine += @crlf

            SET @sCmdLineDel += @crlf    

            SET @sTSql += @crlf          

            SET @sTSqlDel += @crlf

            -- Attach

            IF @i > 1

                  SET @sAttach += ', '

            IF @Usage = 'Rows'

                  BEGIN                  

                 

                        SET @sAttach = @sAttach + '

      (FILENAME = ''' +  dbo.udfAddBackslashIfNec(@DestDataPath) +  @FileNameOnly + ''')'

           

                  END

                 

            ELSE

                  SET @sAttach = @sAttach + '

      (FILENAME = ''' +  dbo.udfAddBackslashIfNec(@DestLogPath) +  @FileNameOnly + ''')'

                 

     

     

      END

      SET @sAttach += '

      FOR ATTACH'

Print '-- Command line copy'

Print @sCmdLine

Print ''

Print '-- T-SQL code'

Print @sTSql

Print ''

Print '-- Attach'

Print @sAttach

Print ''

Print '-- Command line DELETE original files'

Print '-- **** Only run this after database has been attached!!  ****'

Print @sCmdLineDel

Print ''

Print '-- T-SQL DELETE original files'

Print '-- **** Only run this after database has been attached!!  ****'

Print @sTSqlDel

Print '-- DONE'

Print ''

Print ''