Handy Script for moving Databases

Ever have to move your databases to different drives for whatever reason?  For me it's a SAN change-out.

I won't claim it's perfect, but I whipped this up to make it easier to move a database without having to type it all.

/******************************************
Procedure:		MoveDatabases
Author:			mtassin
Date:			2016-02-03
Purpose:		To relocate databases from one location to another
Parameters:
@Databasename	sysname		The name of the database to relocate
@NewLocation	nvarchar(520)	The new location to move the database files to
@NewLogLocation nvarchar(520)   The new location for the Transaction Logs if NULL will use @NewLocation

********************************************/
CREATE PROCEDURE dbo.MoveDatabases
@DatabaseName sysname,
@NewLocation nvarchar(520),
@NewLogLocation NVARCHAR(520)=NULL
AS

IF @NewLocation IS NULL
BEGIN
	PRINT 'You must specify a new location to move to'
	return
END

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName) 
BEGIN
	PRINT 'Invalid Databasename specified'
	RETURN
END

IF @NewLogLocation IS NULL SET @NewLogLocation = @NewLocation

IF OBJECT_ID('tempdb..#sysfiles') IS NOT NULL DROP TABLE #sysfiles
create TABLE #sysfiles(
sysfilekey BIGINT IDENTITY(1,1) PRIMARY KEY,
fileid SMALLINT,
groupid SMALLINT,
size INT,
maxsize INT,
growth INT,
[status] INT,
perf INT,
name sysname,
filename NVARCHAR(520),
newfilename NVARCHAR(520)
)

DECLARE @SQL NVARCHAR(MAX)='Insert into #sysfiles(fileid,groupid,size,maxsize,growth,status,perf,name,filename) select  from [' + @databasename + '].sys.sysfiles'
DECLARE @CRLF NVARCHAR(max) = CHAR(10) + CHAR(13)
DECLARE @RC INT=0

EXEC sp_executesql @SQL

SELECT @RC = COUNT(*)
FROM #sysfiles
WHERE CHARINDEX(@NewLocation,filename) > 0

IF @RC != 0 
BEGIN
	PRINT 'Database has already been moved'
	RETURN
end
IF @DatabaseName = 'master' 
BEGIN
	PRINT 'Moving master requires additional configuration steps.  Please refer to this article'
	PRINT 'https://msdn.microsoft.com/en-us/library/ms345408.aspx#master'
	RETURN
END
UPDATE #sysfiles
SET newfilename = @NewLocation + REVERSE(LEFT(REVERSE(filename),CHARINDEX('\',REVERSE(filename))-1))
WHERE groupid != 0

UPDATE #sysfiles
SET newfilename = @NewLogLocation + REVERSE(LEFT(REVERSE(filename),CHARINDEX('\',REVERSE(filename))-1))
WHERE groupid = 0



SET @SQL = NULL

SELECT 
@SQL = COALESCE(@SQL + 'ALTER DATABASE [' + @DatabaseName +'] MODIFY FILE(NAME=[' + name + '],FILENAME=''' + newfilename + '''); ','ALTER DATABASE [' + @DatabaseName +'] MODIFY FILE(NAME=[' + name + '],FILENAME=''' + newfilename + '''); ' )
FROM 
#sysfiles

EXEC sp_executesql @SQL


IF @DatabaseName = 'tempdb'
BEGIN
	PRINT 'Tempdb can only be moved by a server restart new location for files is set, reset server to continue'
	RETURN
END
SET @SQL = ''

SET @SQL = @SQL + 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '  + @CRLF + 'ALTER DATABASE [' + @DatabaseName + '] SET OFFLINE ' 
EXEC sp_executesql @SQL
SET @SQL = ''

SELECT 
@SQL += 'exec xp_cmdshell ''MOVE /Y ' + filename + ' ' + newfilename  + '''' +@CRLF
FROM 
#sysfiles

EXEC sp_executesql @SQL
SET @SQL = ''


SET @SQL = @SQL + 'ALTER DATABASE [' + @DatabaseName + '] SET ONLINE ' + @CRLF+ 'ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER ' 
EXEC sp_executesql @SQL