Move user databases using xp_cmdshell

An example for using xp_cmdshell... moving user databases.

/*
=========================================================================================================================
 [OBJECT NAME]: Move user databases using xp_cmdshell 
 [DESCRIPTION]: Move user databases using xp_cmdshell  
 [CODE BY]:	Pedro Ferreira

 [NOTES]: 
	more information: http://msdn.microsoft.com/en-us/library/bb934049.aspx
 
 [PARAMETER BREAKDOWN]:
	---

 [SAMPLE CALL]:
	---
 
 [HOTPOINTS]  
	Allow NETWORK SERVICE on the directory where are located the files.

 [SQL Server Version Support]: 2005, 2008, 2008R2, 2012, 2014 
 
 [MODIFICATION HISTORY]:
 Date        Author						   Comment
 ----------  ----------------------------  ------------------------------------------------------------------------------
 03-02-2015  Pedro Ferreira				   Inception

=========================================================================================================================
*/

SET NOCOUNT ON
GO


CREATE DATABASE TestDB
	ON  PRIMARY 
		( NAME = N'TestDB'
		, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf'
		, SIZE = 5120KB
		, FILEGROWTH = 1024KB )
	LOG ON 
		( NAME = N'TestDB_log'
		, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf'
		, SIZE = 1024KB
		, FILEGROWTH = 1024KB
		);
GO

/* more info about sys.master_files at https://msdn.microsoft.com/en-us/library/ms186782.aspx */
SELECT 
	  smf.name				AS N'File Logical Name'
	, smf.physical_name		AS N'File Physical Name'
	, smf.type_desc			AS N'File Type'
FROM
	sys.master_files AS smf
WHERE 
	smf.database_id = DB_ID(N'TestDB');


USE master;
GO


ALTER DATABASE TestDB
	SET OFFLINE;
GO


SELECT
	  sd.name			AS N'Database Name'
	, sd.state_desc		AS N'Database State'
FROM 
	sys.databases AS sd
WHERE
	sd.database_id = DB_ID(N'TestDB');
GO


/* To allow advanced options to be changed */
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

/* To enable xp_cmdshell feature */
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

/* Move Physical Files */
EXEC xp_cmdshell 'XCOPY /Y "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf", "D:\DBs\TestDB"'
GO
EXEC xp_cmdshell 'XCOPY /Y "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf", "D:\DBs\TestDB\Log"'
GO

/* Change  Files */
ALTER DATABASE TestDB
MODIFY FILE
	( NAME = TestDB
	, FILENAME = 'D:\DBs\TestDB\TestDB.mdf'
	);
GO

ALTER DATABASE TestDB
MODIFY FILE
	( NAME = TestDB_log
	, FILENAME = 'D:\DBs\TestDB\Log\TestDB_log.ldf'
	);
GO

SELECT 
	  smf.name				AS N'File Logical Name'
	, smf.physical_name		AS N'File Physical Name'
	, smf.type_desc			AS N'File Type'
FROM
	sys.master_files AS smf
WHERE 
	smf.database_id = DB_ID(N'TestDB');
GO


ALTER DATABASE TestDB
SET ONLINE;


SET NOCOUNT OFF;
GO