Friday, August 5, 2011

Moving the system Database in SQL Server

Moving the system Database in SQL Server

Below are the simple tips for moving system databases to differnet location.

Master Database =>

right-click on SQL Server in Enterprise Manager (EM) and choose Properties.
click the Startup Parameters

Replace the path for -d and -e parameter with new one

-d New patha\msater.mdf.
-l New patha\msatlog.ldf

Stop SQL Server and move the master data and log files to its new location.
start SQL Server and you have successfully moved your Master

Tempdb Database =>
open query analyzer and run the following query:

use master go
Alter database tempdb modify file (name = tempdev, filename = ‘E:Sqldata empdb.mdf’)
Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)

Stop sql server move tempdb files to new location and delete the older one.
Restart sql server

MSDB Database =>

Right-click the SQL-Server name and click properties.
choose your startup parameters.
enter the parameter -T3608.
Restart SQL Server.
detach the database and move them to their appropriate place.
and reattach it

1 comment:

  1. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training