Relocate SQL Server System Databases - SQL 2000

There may be a time when you are required to change the location of the various system databases in SQL 2000- Master, Model, MSDB. Below are several scripts and instructions for manual steps to accomplish this.


/* Change model database file locations */

Stop SQL Server Service
Start SQL Server from command prompt using NET START MSSQLSERVER /c /T3608

--SQL Script begin
--Enable xp_cmdshell
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
--Detach model
use master
exec sp_detach_db 'model', 'true'
GO
--Copy database and transaction log files
exec master.dbo.xp_CmdShell
'(copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf" F:\MSSQL\Data)'
GO
exec master.dbo.xp_CmdShell
'(copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf" "F:\MSSQL\Transaction Logs")'
GO
--Attach model
use master
exec sp_attach_db 'model','F:\MSSQL\Data\model.mdf', 'F:\MSSQL\Transaction Logs\modellog.ldf'
--Disable xp_cmdshell
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE
--SQL Script end



/* Change MSDB database file locations */
--SQL Script begin
--Enable xp_cmdshell
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
--Detach MSDB
use master
exec sp_detach_db 'msdb'
GO
--Copy database and transaction log files
exec master.dbo.xp_CmdShell
'(copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf" F:\MSSQL\Data)'
GO
exec master.dbo.xp_CmdShell
'(copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf" "F:\MSSQL\Transaction Log"s)'
GO
--Attach MSDB
use master
exec sp_attach_db 'MSDB','F:\MSSQL\Data\MSDBData.mdf', 'F:\MSSQL\Transaction Logs\MSDBLog.ldf'
--Disable xp_cmdshell
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE
--SQL Script end


Remove the -c -T3608 startup parameters
Stop SQL Server
Start SQL Server Service



/* Change master database log location */

In Startup Parameters change -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf to -dF:\MSSQL\Data\master.mdf
In Startup Parameters change -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf to -lF:\MSSQL\Transaction Logs\mastlog.ldf

Stop SQL Server

Manually copy master.mdf from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ to F:\MSSQL\Data
Manually copy master.ldf from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ to F:\MSSQL\Transaction Logs

Start SQL Server



/* Verify file locations */

--SQL Script begin
EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; EXEC sp_helpfile;'
--SQL Script end



/* Remove old files */

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.ldf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.ldf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.ldf


Script file for queries used in this article is here:
http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/sql-server-scripts/RelocateSystemDatabases.sql?attredirects=0

All SQL script files are here:
http://blogfiles.karamazovgroup.com/Home/sql-server-scripts