How to Move sql system databases

Posted: April 13, 2009 in Sqlserver

 

/*–Get the sqlserver database logical file names by running the below query–**/

Select name,filename from sys.sysaltfiles
/*—Put the Sqlserver in single user mode to move all the system databases.

1.Stop the sqlserver service before you put sql in single user mode
2.Open command prompt
3.Locate Binn folder
4.Run below command to put sql in single user mode

**/

 

D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr -c -m -T3608
/*—How to move Master database in Sqlserver2005 ?
Please follow the below steps to do this
Moving the master database

Change the path for the master data files and the master log files using SQL Server Enterprise Manager for Sql2000 and for SQl2005 with the help of SCM( Sqlserver Configuration Manager).

Note You may also change the location of the error log here.

Right-click the SQL Server in SCM and then click Properties.

Goto Advance Tab

Click on Startup Parameters to see the following entries:

-dD:\MSSQL7\data\master.mdf
   -eD:\MSSQL7\log\ErrorLog
   -lD:\MSSQL7\data\mastlog.ldf

-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.

 

Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location:

-dE:\SQLDATA\master.mdf
      -lE:\SQLDATA\mastlog.ldf

Stop SQL Server.
Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
Restart SQL Server.

 

/*—For moving MSDB database we need to even move model database.**/
–Move model database
–Detach the database

use master
go
sp_detach_db ‘model’
go

–Attach the database

Use Master
go
sp_attach_db ‘Model’,’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDataPath\model.mdf’,’D:\Program Files\Microsoft SQL  Server\MSSQL.1\MSSQL\NewDataPath\modellog.ldf’
go
–Move MSDB database
–Detach the database

use master
go
sp_detach_db ‘msdb’
go

–Attach the database
use master
go
sp_attach_db ‘msdb’,’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDataPath\msdbdata.mdf’,’D:\Program Files\Microsoft SQL  Server\MSSQL.1\MSSQL\NewDataPath\msdblog.ldf’
go

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s