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 wiriting 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

b.) Restart services.
c.) Confirm path of 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

USE master;
GO
alter database model MODIFY FILE (NAME = modeldev,FILENAME='NEW PATH');
go
alter database model MODIFY FILE (NAME = modellog,FILENAME='NEW PATH');
go

b.) Stop services
c.) Copy the files to the new location
d.) Restart services.
e.) Confirm path of database files.

3.) Moving master database:

a.) Edit the startup parameters to reflect the new path for –d, –l and -e parameters.
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
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)
f.) Execute the script given below from sqlcmd

USE master;
GO
alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME='NEW PATH\mssqlsystemresource.mdf');
go
alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME='NEW PATH\mssqlsystemresource.ldf');
go
alter database mssqlsystemresource set READ_ONLY;
go

g.) Stop the services
h.) Change the port back to 1433
i.) Remove the alias
j.) Start sql services.
k.) Confirm if the data files and log files reside on desired path.