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)