Move Database Files Script

Been a while since my last post, it has been an incredibly busy past couple of months. Getting back into the swing of things I wrote this script to make moving database files around a little easier and less time consuming. This was developed in conjunction with a project I am working on where there are hundreds of SQL Servers and each of  them has their database files scattered into the winds. It literally looks like someone took all the database files loaded them into a shotgun and blasted the server with them. Moving the databases to standardized locations is one of the many tasks I have to perform on all of these servers and moving all of those files was just to much to do by hand.


/*
Title: Move Database Files
Created: September 8th, 2014
By: Christopher Provolt

Input: Change the values for the following variables
@new_data_file_path - Path to where database data files should be moved to
@new_log_file_path - Path to where database log files should be moved to

Notes:
Ensure that the paths are set correctly prior to running scripts
Change output to text (Ctrl + T or Query > Results To > Results To Text)
Last Update
Name:
Date:

Change Log
------------------------------------------------------------------------------------------
8/9/2014 - Initial script
*/

set nocount on;

declare @new_data_file_path varchar(300)
declare @new_log_file_path varchar(300)

/* Define the new datapaths for the files to be moved to
*/
set @new_data_file_path = LOWER('<New Data File Path, Nvarchar(300),e:\mssql\data>')
set @new_log_file_path = LOWER('<New Log File Path, Nvarchar(300),f:\mssql\logs>')

/* Dynamic table to hold information on database files and their locations
we will use this data to create the required move scripts
*/

declare @info table
(
id int primary key identity,
database_id int,
dbname varchar (50),
virtual_file_name varchar(50),
type_desc varchar(10),
db_file_name varchar(50),
file_path varchar(300)
)

/* Dynamic table to hold all the scripting lines created.
This table is cleared after the data files are processed.
Steps: 1,change file location, 2, take db offline, 3, copy files, 4,database online
*/

declare @scripts table
(
step int,
scriptText varchar(300)
)

/* Dynamic table to store the changes that are to be made.
This data can be pasted into an email to inform customer of the changes
*/

declare @summary table
(
actions varchar(30),
dbname varchar(50),
file_type varchar(10),
db_file_name varchar(50),
old_path varchar(300),
new_path varchar(300)
)
/* Variable Decleration Area */
declare @id int; --id is created on temp table to use in cursor for processing data
declare @old_file_location varchar(1000) --temporarily stores the old file location for comparisons
declare @tmpString varchar(1000) -- used to temporarily hold strings
declare @temp_dbname varchar(100) -- holds temp database name
declare @temp_virtual_file_name varchar(100) -- holds temp virtual file name
declare @temp_file_type varchar(10) -- holds temp file type either ROWS or LOG
declare @temp_file_name varchar(100) -- holds temp file name
declare @temp_old_file_location varchar(1000) --holds old location of the file

/* get required data and place into @info dynamic table */
insert into @info
select mf.database_id,
db.name as 'dbname',
mf.name as 'virtual_file_name',
mf.type_desc,
REVERSE(SUBSTRING(REVERSE(mf.physical_name), 0, CHARINDEX('\', REVERSE(mf.physical_name)))) as 'db_file_name',
LOWER(SUBSTRING(mf.physical_name, 0, LEN(mf.physical_name) - CHARINDEX('\', REVERSE(mf.physical_name)) + 1)) as 'file_path'

from sys.master_files mf
INNER JOIN sys.databases db ON mf.database_id = db.database_id
where mf.database_id NOT IN
(Select database_id from sys.databases
where sys.databases.name in ('tempdb','master','msdb','model','distribution'))
/* Create the scripted commands for the data files */
PRINT('MOVE DATA FILES');

/* Create a cursor on @info table where type_desc is ROWS */
declare c cursor for select id from @info where type_desc = 'ROWS'
open c
fetch next from c into @id

while @@fetch_Status=0 Begin

/* Assign required values into variables from @info db */
select @old_file_location = i.file_path,
@temp_virtual_file_name = i.virtual_file_name,
@temp_file_name = i.db_file_name,
@temp_dbname = i.dbname,
@temp_file_type = type_desc,
@temp_old_file_location = i.file_path,
from @info i
where id = @id

/* check if we need to move the files */
if @old_file_location <> @new_data_file_path
/* if we have to move files generate the scripts */
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_data_file_path + '\' + @temp_file_name+ ''')';
insert into @scripts values(1, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(2, @tmpString)
set @tmpString = 'Xcopy /O /-Y /J /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_data_file_path + '\' + @temp_file_name + '"'
insert into @scripts values(3, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(4, @tmpString)

/* Update the @summary table to reflect the change */
insert into @summary values('MOVE DATA FILE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_data_file_path)
end
else
begin
/* if there is no update neccessary update the summary table */
insert into @summary values('NO CHANGE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_data_file_path)
end
fetch next from c into @id
End

/* Close and deallocate the cursor so we can use it again for dealing with log files */
close c
deallocate c

/* Display the scripts to run */
select scriptText from @scripts order by step, scriptText

/* Erase the @scripts table prior to adding log file scripts */
delete from @scripts where scriptText <> '';
/* Create the scripted commands for the log files */

PRINT('MOVE LOG FILES')
/* Create a cursor on @info table where type_desc is LOG */
declare c cursor for select id from @info where type_desc = 'LOG'
open c
fetch next from c into @id

while @@fetch_Status=0 Begin
/* Assign required values into variables from @info db */
select @old_file_location = i.file_path, @temp_virtual_file_name = i.virtual_file_name, @temp_file_name = i.db_file_name, @temp_dbname = i.dbname, @temp_file_type = type_desc, @temp_old_file_location = i.file_path from @info i where id = @id

/* check if we need to move the files */
if @old_file_location <> @new_log_file_path
/* if we have to move files generate the scripts */
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_log_file_path + '\' + @temp_file_name + ''')';
insert into @scripts values(1, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(2, @tmpString)
set @tmpString = 'Xcopy /O /-Y /J /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_log_file_path + '\' + @temp_file_name + '"'
insert into @scripts values(3, @tmpString)
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(4, @tmpString)

/* Update the @summary table to reflect the change */
insert into @summary values('MOVE LOG FILE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_log_file_path)

end
else
begin
/* if there is no update neccessary update the summary table */
insert into @summary values('NO CHANGE', @temp_dbname, @temp_file_type, @temp_file_name, @temp_old_file_location, @new_log_file_path)
end

fetch next from c into @id
End

/* Display the scripts to run */
select scriptText from @scripts order by step, scriptText

/* Print the summary */
PRINT('SUMMARY')
select * from @summary
/* cleanup */
close c
deallocate c

Advertisement