Categories
Blog Administration
Powered by

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
Trackbacks
Trackback specific URI for this entry