It isn't default enabled on SQL Server Instances.
Be sure to enable this feature before running the script.
You can do it issuing these commands:
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
This script accepts 2 parameters:
@Dbname - Data Base Name to move
@UseDFDir - Use the SQL Server Instance Defaults paths 0 = No, 1 = Yes
If you are not willing to use the defaults be sure to set the correct paths in the script after the header:
declare @new_data_file_path nvarchar(300) = LOWER('f\SQLDati')
declare @new_log_file_path nvarchar(300) = LOWER('l:\SQLLogs')
You can check/set the default DB paths in the SQL Server properties
The script creates the dbo.movedbfiles StorePorcedure you can use launch it like this:
exec dbo.movedbfiles 'logtest', 1
------------------------- Script Start ------------------------------
create PROCEDURE dbo.MoveDBFiles @DBname varchar(300), @UseDFDir int = 0
AS
set nocount on;
/*
Title: Move Database Files
Created: 18-01-2019
By: Ruggiero Lauria
Steps: 1,change file location, 2, take db offline, 3, copy files, 4,take database back online
Input: Change the values for the following variables
@Dbname - Data Base Name to move
@UseDFDir - Use the SQL Server Instance Defaults paths 0 = No, 1 = Yes
Example: exec dbo.MoveDBFiles 'Db1' , 1
Notes:
Ensure that paths are set correctly prior to running scripts
Ensure you have enabled the xp_cmdshell on your instance
The script is provided as it is and for free, use it at your own risk.
Feel free to modify and distribute it
Credits:
Thanks to Christopher Provolt for the initial script template I have used to create mine:
https://cprovolt.wordpress.com/2014/09/10/move-database-files-script/
*/
/* Set your desired Directory if you ar not using the SQL Server Instance Defaults paths @UseDFDir = 0 */
declare @new_data_file_path nvarchar(300) = LOWER('f\SQLDati')
declare @new_log_file_path nvarchar(300) = LOWER('l:\SQLLogs')
/* Temp Table to hold script messages */
declare @message table
(
msg_num int,
message_text nvarchar(300)
)
/*Check if the DB exists and is not a System Database */
if (select database_id from sys.databases where name= @dbname) < 5 or (select count(*) from sys.databases where name= @dbname) = 0
insert into @message values((select count(*) from @message) + 1, 'Please check you DB Name, consider this procedure does not process System Databases')
else
begin
insert into @message values((select count(*) from @message) + 1, 'Processing Database: ' + @DBname )
/* Load the Instance default paths settings */
if (@UseDFDir =1)
begin
set @new_data_file_path = (select left(cast(serverproperty('InstanceDefaultDataPath') as varchar(100)), len(cast(serverproperty('InstanceDefaultDataPath') as nvarchar(100)) ) -1))
set @new_log_file_path = (select left(cast(serverproperty('InstanceDefaultLogPath') as varchar(100)), len(cast(serverproperty('InstanceDefaultLogPath') as nvarchar(100)) ) -1))
end
/* 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.
Steps: 1,change file location, 2, take db offline, 3, copy files, 4,database online
*/
declare @scripts table
(
step int,
scriptText varchar(300),
commtype char(4)
)
/* 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
*/
/* 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_name varchar(100) -- holds temp file name
declare @temp_old_file_location varchar(1000) --holds old location of the file
declare @new_file_path varchar(1000) --holds new location of the file
declare @num int = 0 -- Internal row counter
declare @numrows int -- Total number of files to move for the specified DB
declare @command nvarchar(1000) -- used to temporarily hold command to launch
declare @commtype char(4) -- used to temporarily hold command type to launch
/* 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 IN
(Select database_id from sys.databases
where sys.databases.name = @dbname)
/* Create the scripted commands for the files */
/* Count the number of files to move */
set @numrows = (select count(*) from @info )
/* Create a cursor on @info table */
declare c cursor for select id from @info
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,
@new_file_path = case i.type_desc when 'ROWS' then @new_data_file_path when 'LOG' then @new_log_file_path else '' end,
@temp_file_name = i.db_file_name,
@temp_dbname = i.dbname,
@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_file_path and @new_file_path <>'')
begin
-- select @old_file_location, @new_file_path
/* if we have to move files generate the scripts */
set @num = @num +1
select @num as num, @numrows as numrows
set @tmpString = 'ALTER DATABASE [' + @temp_dbname + '] MODIFY FILE (NAME=[' + @temp_virtual_file_name + '], FILENAME=''' + @new_file_path +'\' + @temp_file_name+ ''')';
insert into @scripts values(@num, @tmpString, 'TSQL')
set @tmpString = 'MOVE /Y "' + @old_file_location + '\' + @temp_file_name + '" "' + @new_file_path +'\' + @temp_file_name + '"'
insert into @scripts values(@num+@numrows+1, @tmpString, 'CMD')
end
fetch next from c into @id
End
if @num > 0
begin
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET OFFLINE WITH ROLLBACK IMMEDIATE';
insert into @scripts values(@numrows +1, @tmpString, 'TSQL')
set @tmpString = 'ALTER DATABASE [' + @temp_dbname +'] SET ONLINE';
insert into @scripts values(@numrows * 2 +2, @tmpString, 'TSQL')
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 * from @scripts order by step
/* Executes the commands */
if @num > 0
begin
declare c cursor for select scriptText , commtype from @scripts order by step
open c
fetch next from c into @command, @commtype
while @@fetch_Status=0 Begin
if @commtype = 'TSQL'
begin
insert into @message values((select count(*) from @message) + 1, 'Executing TSQL: ' + @command)
EXECUTE sp_executesql @Command;
end
else
begin
insert into @message values((select count(*) from @message) + 1, 'Executing XP Command: ' + @command)
EXEC xp_cmdshell @Command;
end
fetch next from c into @command, @commtype
End
close c
deallocate c
end
else
insert into @message values((select count(*) from @message) + 1, 'DB Files are already in the desired Directories. Nothing to do!' )
end
select message_text from @message order by msg_num
go
------------------------- Script end ------------------------------
