Moving System Databases files

Moving System Databases files from old location to new location

Some times after instalation of the sql server, we need to change the system databases file locations. the below process guide us to change the system databases files locations from old to new.

Moving the  Tempdb,Msdb and Model database files from old to new location:

with help of the below command we can move the system databases files from old to new locations logically (not physically).

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

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

1. Now stop the sql server services to move the system databases files from old to new locations physically. 
2. after moving the system databases files physically, start the sql server services and with help of below command we can confirm the databases new location.


USE msdb;
SELECT physical_name from sys.database_files
USE model;
SELECT physical_name from sys.database_files

 Note: no need to move the tempdb files physically, because it's recrate always when sql services are start.

 Moving the Master and Resources database files:

 Edit the startup parameters to reflect the new path for –d(datafile), –l(logfile) and -e(sql log file) parameters.

go to start ----> all programs ----> Micro soft sql server 2005 ----> configuration tool ----> sql server configuration manager 

 After this we need to follow the below steps for sql server 2005, why because the resource database is depended on master database. so, we need to put the resource database files at master database files location.

Note: above sql server 2005 version no need to follow the below steps. just restart the sql services. because the resources database is independent.

1. Stop the services.
2. Move the master and resource database files to the new location
3. 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)

 open the new query window and run the below command.(don't open the sql instance in management console). other wise run the below command in 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

 stop and start the sql server services and confirm the new locations.

Note: Please post your comments...

Happy Learning