Moving SQL 2005 System Databases - Step By Step Instructions

Moving SQL System Database Files

MASTER DATABASE

1) Update the –d –l and –e paths on the startup parameters to the intended new location

-dE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2) Shutdown sql server

3) Move the master.dbf and master.ldf files

Ensure that the log file directory in the new location is created ready

4) DO NOT STARTUP THE DB AT THIS POINT (THE MASTER AND RESOURCE DATABASE HAVE TO BE MOVED AND LOCATED IN THE SAME DIRECTORY!)

RESOURCE DATABASE

1) Run net start mssqlserver /f /T3608 from command prompt to start the server in single user and master recovery only mode

2) Close the object explorer in SQL Server Management Studio (as this uses your one connection)

3) ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf');

GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf');
GO

4) ALTER DATABASE mssqlsystemresource SET READ_ONLY;

5) Shutdown the DB

6) Move the files to the new location

7) Restart the services normally

8)
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

TEMP DATABASE

1)
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf');
GO

2) Restart the services

3) Verify files exist in new location

4)
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

5) Delete files from old location

MODEL AND MSDB DATABASES

1) ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf' )

2) ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf' )

3) ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf' )

4) ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf' )

5) Shutdown the sql services

6) Move the files to the new location

7) Startup the instance again

8) Check the new paths using the following statements…

use model
go
sp_helpfile
go

use msbd
go
sp_helpfile
go

Good luck!

Migrating agents between DPM servers

To migrate your DPM protected servers from one DPM parent server to another, follow the procedure below...

On the client machine (agent) to re-targeted.

1) Open a CMD promt, and CD to "C:\Program Files\Microsoft Data Protection Manager\DPM\bin"

2) Type "SetDpmServer.exe -dpmservername yourdpmservername"

On the new DPM server you wish to use

1) Open a powershell prompt, and run "attach-productionserver.ps1"

2) Answer the prompts!

See the screenies below!