Move Database Files Script

10 09 2014

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

Advertisements




Restore latest backup of database to same server as new database

30 07 2014

Some, in order to save money, do not purchase a second server for development. If development activities do not require changes to the database system then it is not a horrible idea to just use the same server. Other usage of this script is to have a copy of the production database for a sandbox, reporting, or testing. Here I wrote this script that will run in a maintenance plan to update or create a development database based off of the latest full backup of the source or production database.

/*
Name: Backup_Restore_Maintenance_Plan
Desc: Allows for a single database, the source, to be restored as a new database,
	the target, on the same server
	
Author: Christopher Provolt
Date Created: 29JULY2014

Notes:
Ensure source / target databases and the paths for the data / log files are set correctly 
prior to executing this script

*/
USE master
DECLARE @name			varchar(100)
DECLARE @location		varchar(500)
DECLARE @log_path		varchar(500)
DECLARE @data_path		varchar(500)
DECLARE @log_logical	varchar(100)
DECLARE @data_logical	varchar(100)
DECLARE @target			varchar(100)
DECLARE @source			varchar(100)
DECLARE @datapath		varchar(300)
DECLARE @logpath		varchar(300)

/* Define source and target databases */
set @source = 'AdventureWorks2012'
set @target = 'AW2'

/* define paths and file names for data and log files for target db */
set @datapath = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Data\' + @target +'.mdf'
set @logpath = 'F:\MSSQL12.MSSQLSERVER\MSSQL\Logs\' + @target + '_log.ldf'

/* determine the location of the latest backup for the source DB */
SELECT  @name = x.database_name,
        @location = x.physical_device_name
        
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1 AND x.database_name = @source

 /* get logical names of the source database files for recovery */
select @data_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'ROWS'
select @log_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'LOG'

/*Restore the database to its new home */
RESTORE DATABASE @target FROM DISK=@location
WITH REPLACE,
MOVE @data_logical TO @datapath, 
MOVE @log_logical TO @logpath