script to move a database

This script will move the data and log files for a single database.
You need to set the database name and new paths before running.


--SCRIPT TO MOVE INDIVIDUAL DATABASE FILES

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @DATABASE_NAME='TWO',
@NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'



DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)


SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH


SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', 
  FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', 
  FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

script to move all DYNAMICS databases

I frequently have to move data and log files after GP setup to get them into the best practices configuration.  I decided to write a script to move all the common dynamics related databases to new data and log file locations.
This script assumes the system database is named DYNAMICS.
It then handles DYNAMICS, all company databases, resco, report server, smartconnect, and nodebuilder.

--script to move all DYNAMICS databases
--set the new paths and growth rates before running

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'

DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)

DECLARE C_INTERID CURSOR FOR
SELECT RTRIM(INTERID)
FROM DYNAMICS.dbo.SY01500 D
INNER JOIN sys.sysdatabases S
ON D.INTERID=S.name
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME ='DYNAMICS'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'resco%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'ReportServer%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'SmartConnect%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'NodeBuilder%'

OPEN C_INTERID
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH


SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', 
 FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', 
 FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME

END

CLOSE C_INTERID
DEALLOCATE C_INTERID