Moving System Databases – SQL Server 2005

There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought of writing an article for moving the databases so that the users can use the same and enjoy the task.

Tasks for moving system databases:

1.Moving tempdb databases.

a.) Execute the script below.

USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev,FILENAME='NEW PATH');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog,FILENAME='NEW PATH');
GO

Example

b.) Restart services.

c.) Confirm path of database files using the query

USE tempdb; 
SELECT physical_name FROM sys.database_files

2. Moving model and msdb databases.

a.) Execute the script below.

USE master;
GO
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData,FILENAME='NEW PATH');
GO
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog,FILENAME='NEW PATH');
GO
ALTER DATABASE model MODIFY FILE (NAME = modeldev,FILENAME='NEW PATH');
GO
ALTER DATABASE model MODIFY FILE (NAME = modellog,FILENAME='NEW PATH');
GO

Example

b.) Stop services

c.) Copy the files to the new location

d.) Restart services.

e.) Confirm path of database files using the below query.

USE msdb; 
SELECT physical_name FROM sys.database_files
GO
USE model; 
SELECT physical_name FROM sys.database_files
GO

3.) Moving master database:

a.) Edit the startup parameters to reflect the new path for d, l and -e parameters.

Example

b.) Stop the services.

c.) Move the master and resource database files to the new location

d.) Change the sql port to different one than usual to avoid excess connections and create an alias with that port (You can skip this step if you doesnt get excess connection error)

e.) Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)

Example

f.) Execute the script given below from sqlcmd

USE master;
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = DATA,FILENAME='NEW PATHmssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = LOG,FILENAME='NEW PATHmssqlsystemresource.ldf');
GO
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
GO

Example

.) Stop the services

h.) Change the port back to 1433(Skip this step if you have not done Step d)

i.) Start sql services.

j.) Confirm if the data files and log files reside on desired path using the query

SELECT physical_name FROM sys.master_files