You may find yourself in a position that requires you to move the data and log files for a particular set of databases to a new directory or disk. When in this position you have several methods to choose from to perform the task at hand. You may perform a backup\restore of the databases, you might detach and attach the databases or you can use the Alter Database Modify File option.

If you choose the backup\restore option you must backup the original databases then delete or rename the original databases and restore the backups to the new location. This option would work but is time consuming especially if your databases are large. You must run the backup first; this could take several minutes to several hours depending on size. You must delete or rename the original databases. Then you must restore the database; again several minutes to several hours.

If you have selected the Detach\Attach method you will have saved some time as you will only need to move the log and data files, no need to completely backup the database and then restore it. You will need to re-enable Service Broker if you are using it and if the trustworthy flag was set on the database you will need to enable\reset that as well as both of these settings are lost when using the detach\attach method.

The quickest option is the Alter Database Modify File option. No need to backup the files first so it saves time, no need to re-enable settings that are lost in the Detach\Attach method and lastly it is the preferred method by many DBA's. I have created a script that uses this method. The script allows you to pass a new folder for the data files, a new folder for the log files and a comma separated list of databases that you wish to move.

To begin first let's create 3 basic databases with the create database command. These databases will be created in your default log and data folders. Copy the code below and paste it into a new query window inside Microsoft SQL Server Management Studio (SSMS) then click on the execute button.

Create database db1

Create database db2

Create database db3

Now let's install the procedure that will move the three databases. Cut and paste into SSMS and click the execute button.

/*

Author=James DiMauro

This procedure will move data and log files for a list of databases using alter database. This procedure assumes 

the directories passed exist and that the SQL service account has full control NTFS permissions to the directories. It also assumes

the database names passed are valid databases for the instance.
Usage = exec p_MoveDatabase 'c:\sqlmovetest\','c:\sqlmovetest\','db1, db2, db3'

*/

IF ( OBJECT_ID('dbo.p_MoveDatabase') IS NOT NULL ) 

begin

   DROP PROCEDURE dbo.p_MoveDatabase

end

GO

create procedure p_MoveDatabase
        

  @NewDataFolder nvarchar(1000),

    @NewLogFolder nvarchar(1000),

     @DbList nvarchar(4000) 
as
        Begin
        declare @DbTable table (lkey int identity (1,1) primary key, dbname nvarchar(100))

        declare @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int )

    declare @sql nvarchar(4000)

       declare @count int,

                       @RowNum int

       declare @DbName nvarchar(100)

     declare @OldPath nvarchar(1000)

   declare @Type int

 declare @LogicalName nvarchar(100)

        declare @ParmDefinition nvarchar(1000)

    declare @FileName nvarchar(100)

   declare @NewPath nvarchar(1000)

   declare @ShowAdvOpt int

   declare @XPCMD int
        set nocount on;

        --insert into @DbTable (dbname) values ('testdb1')

        --insert into @DbTable (dbname) values ('testdb2')
if right(@DbList,1) = ','

Begin

      print 'DbList must NOT end with "''"'

     return

End
declare @MyString NVARCHAR(100)

declare @Pos INT

declare @NextPos INT

declare @String NVARCHAR(4000)

declare @Delimiter NVARCHAR(1)
set @String = @DbList

set @Delimiter = ','
SET @String = @String + @Delimiter

SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)

BEGIN

      SET @MyString = substring(@String,1,@Pos - 1)


       insert into @DbTable (dbname) values (LTRIM(RTRIM(@MyString)))
        SET @String = substring(@String,@pos+1,len(@String))

      SET @pos = charindex(@Delimiter,@String)

END 
        set @ShowAdvOpt = cast(( select [value] from sys.configurations where [name] = 'show advanced options') as int)

   set @XPCMD = cast(( select [value] from sys.configurations where [name] = 'xp_cmdshell') as int)
        if right(@NewDataFolder,1)<> '\' or  right(@NewLogFolder,1)<>'\' 

 Begin

             print 'new path''s must end with \'

               return

    end
        EXEC sp_configure 'show advanced option', '1'

     RECONFIGURE

        exec sp_configure 'xp_cmdshell' , '1'

     RECONFIGURE
        print 'NewMdfFolder is ' + @NewDataFolder 

        print 'NewLdfFolder is ' + @NewLogFolder 
        SET @RowNum = 1

   SET @count = (select count(*) from @DbTable)


        while @RowNum <= @count

   Begin
                select @DbName = DBName from @DbTable

             where lKey = @RowNum


                set @sql = 'select name, physical_name, type from  ' + @DbName + '.sys.database_files'
                insert into @FileTable

            exec sp_executesql @sql

           

                -- kill all user connections by setting to single user with immediate

             set @sql=  'ALTER DATABASE [' + @DbName  + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

            print ''

          print 'Executing line  -' + @sql

          exec sp_executesql @sql
                -- set db off line

                set @sql = 'ALTER DATABASE [' + @DbName + '] SET OFFLINE;' 

               print ''

          print 'Executing line - ' + @sql

          exec sp_executesql @sql
                select * from @FileTable

          while @@rowcount > 0    

          begin

                     select top 1 @OldPath = physical_name, @Type = [type], @LogicalName = [name] from @FileTable
                        --move physical files

                     set @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX('\', REVERSE(@OldPath), 1))))


                     if @type = 0

                              begin

                                     set @NewPath = @NewDataFolder + @FileName

                         end

                       else

                              begin

                                     set @NewPath = @NewLogFolder + @FileName

                          end
                        set @Sql = 'EXEC master..xp_cmdshell ''MOVE "' + @OldPath + '" "' + @NewPath +'"'''     

                  print ''

                  print 'Executing line  -' + @sql

                  exec sp_executesql @sql
                        --alter file paths

                        set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = ' + @LogicalName + ', FILENAME = "' + @NewPath   + '")'

                    exec sp_executesql @sql 
                        delete from @FileTable where [name] = @LogicalName


                  select * from @FileTable
                end --while

                set @sql = 'ALTER DATABASE [' + @DbName + '] SET ONLINE;'

         print ''

          print 'Executing line  -' + @sql

          exec sp_executesql @sql
                SET @RowNum = @RowNum + 1
                -- allow multi user again.

                set @sql=  'ALTER DATABASE [' + @DbName  + '] SET MULTI_USER'

             print ''

          print 'Executing line  -' + @sql

          exec sp_executesql @sql
        end
        exec sp_configure 'xp_cmdshell' , @XPCMD

  reconfigure
        EXEC sp_configure 'show advanced option', @ShowAdvOpt

     RECONFIGURE
End --procedure

Before running this procedure there a couple things you want to be sure of. The folders that you pass to the procedure must exist and the SQL Server service account must have the needed permissions on these folders. There are not any checks in the script to verify the folders exist or that the needed NTFS permissions are present. If the folders do not exist the physical files will not be moved, however the paths in SQL server will still be adjusted, so when the database is attempted to be brought back online it will fail.

If the proper permissions are not present, the same could happen, or possibly if write permissions are present but not read the files could be moved and the sql server updated appropriately yet sql server will not be able to read the files. Certainly take some time and perform some testing to verify the proper permissions are present and that the folders exist. Better yet you may want to adjust the script to use the undocumented procedure xp_FileExist. This will take care of the directory being present. If anyone knows how to verify permissions short of writing files with SQL server and then verifying they were actually written I would like to see an article on that.

Now we have the procedure installed let's call the procedure to move the databases to our new location. Don't forget to create the folders using windows explorer or some other method prior to calling the procedure. In this example if have created 2 folders sqldata and sqllog prior to calling the below script. I have also verified the SQL Server service account has the proper permissions on the folders.

Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Anyone connected will be kicked off. The script does set the db back to multi user when it brings the database back online after the move.This script also turns on and uses the xp_cmdshell advanced option. It will set this option back to its original setting (enabled or disabled) at the end of the script.

exec p_MoveDatabase @NewDataFolder = 'c:\sqldata\', @NewLogFolder= 'c:\sqllog\',@DbList = 'db1, db2, db3'

If successful the files associated with the databases db1, db2 and db3 should have been moved to the passed folders.