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.