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!

1 comment:

  1. “It was macho guys working in the oil field, and football, and oil and grease and adidas factory outlet sand and being a stud and being cool,” Orbison said later. “It was tough as could be, but no illusions, you know? No mysteries in Wink.” Orbison wasn’t popular; later he said he felt “totally anonymous, adidas store even at home.” He started wearing glasses at age four. When he tried out for the Wink Kittens, the junior high version of the Wink Wildcats high school football team, the helmets didn’t have face guards, adidas originals online and his glasses kept falling off. He didn’t make the team.

    ReplyDelete