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.