MOVING MASTER, MSSQL SYSTEMRESOURSE DB PRIMARY AND LOG FILES FROM ONE DRIVE TO ANOTHER DRIVE IN THE SAME MACHINE IN MSSQL Server 2005

MOVING MASTER, MSSQL SYSTEMRESOURSE DB PRIMARY AND LOG FILES FROM ONE DRIVE TO ANOTHER DRIVE IN THE SAME MACHINE IN MSSQL Server 2005

1.Goto Start > All programs > Microsoft sq l server 2005 > Configuration Tools > sq l server configuration manager.
2.Right Click on MSSQLSERVER service and click ADVANCED Tab.
Check the START PARAMETER and change the master primary and log file from old location to new location with the file name.(refer below command)

-dD:\MSSQLSystemDB\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\MSSQLSystemDB\mastlog.ldf

where -d is primary file path and -l is log file path

3.Stop the MSSQLSERVER Services.
4.Move the Master and Mssqlsystemresource DB Primary and Log file into New Location(As mentioned in the START PARAMETER)
5.Start the MSSQLSERVER SERVICE in Recovery-mode. Type below command in the cmd-Prompt
> NET START MSSQLSERVER /f /T3608

For a named instance, run the following command
> NET START MSSQL$instancename /f /T3608

6.Goto Start > All programs > Microsoft sq l server 2005 > MS server Managent Studio. Connect the Instance. Click on New Query.

7.Alter the Resource DB primary file and log file.Execute the Query.

–SYSTEMRESOURCE DB Primary File
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘new_path_of_master\mssqlsystemresource.mdf’);
GO
–SYSTEMRESOURCE DB Log File
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘new_path_of_master\mssqlsystemresource.ldf’);
GO

8.Set the MSSQLSYSTEMRESOURCE DB as READ-ONLY mode
alter database mssqlsystemresource set read_only;

9.Stop and Start the service the MSSQLSERVER service
10. Check the Master files.
sp_helpdb master

Note:Master and mssqlsystemresource primary and log files are in the same location.

Moving model,msdb,tempdb(SYSTEM Databases) from one location to another location in the same machine.(1 hour downtime)

1.For reference, Copy the primary,secondary and log files location of model,msdb and tempdb

USE master
Go

SELECT
DB_NAME(database_id) AS “Database Name”
, name AS “Logical File Name”
, physical_name AS “Physical File Location”
, state_desc AS “State”
FROM
sys.master_files
WHERE
database_id IN (DB_ID(N’msdb’), DB_ID(N’model’),DB_ID(N’tempdb’))
ORDER BY
DB_NAME(database_id);
Go

2.Modify the data and log filename for new location of db files by using Alter database. Have to done for each datbases of files.Execute the Query.

USE master
Go

ALTER DATABASE model
MODIFY FILE ( NAME = ‘modeldev’ , FILENAME = ‘D:\MSSQLSystemDB\model.mdf’ );
Go

ALTER DATABASE model
MODIFY FILE ( NAME = ‘modellog’ , FILENAME = ‘D:\MSSQLSystemDB\modellog.ldf’ );
Go

ALTER DATABASE msdb
MODIFY FILE ( NAME = ‘MSDBData’ , FILENAME = ‘D:\MSSQLSystemDB\MSDBData.mdf’ );
Go

ALTER DATABASE msdb
MODIFY FILE ( NAME = ‘MSDBLog’ , FILENAME = ‘D:\MSSQLSystemDB\MSDBLog.ldf’ );
Go

ALTER DATABASE tempdb
MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘D:\MSSQLSystemDB\tempdb.mdf’);
Go

ALTER DATABASE tempdb
MODIFY FILE (NAME = ‘templog’, FILENAME = ‘D:\MSSQLSystemDB\templog.ldf’);
Go

3.Stop MSSQLSERVER Services.
4.Move the model,msdb and tempdb primary and log files into new location.
5.Start MSSQLSERVER Services.
6.Check the System DB files.

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in MsSQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s