Happy New Year 2011

Wishing you all a very happy and prosperous new year.

Moving master database

Moving master database is a bit different process than moving any other system database.This has to be carried out very carefully and here is the process on how to move the master database.
Before doing anything, make sure you are having proper backups.

1. Stop the SQL Services.
2. Go to the "SQL Server Configuration Manager".
   In SQL Server 2005, Click "Start" >> "All Programs" >> "Microsoft SQL Server 2005" >> "Configuration Tools" >> SQL Server Configuration Manager
   In SQL Server 2008, Click "Start" >> "All Programs" >> "Microsoft SQL Server 2008" >> "Configuration Tools" >> SQL Server Configuration Manager
3. In the left pane, click on "SQL Server Services" 
4. Now in the right pane, select the SQL Server Service component  (which looks like "SQL Server (InstanceName)" ) and go to its properties.
5. In the "Properties" page, go to the "Advanced" tab
6. In the "Startup Parameters", click on the drop list and modify the parameters -d and -l to the new location where you want the master data file (master.mdf) and log file (mastlog.ldf) to reside respectively.
   
   -d stands for the fully qualified data file path of master database.
   -l stands for the fully qualified log file path of master database.
   -e stands for the fully qualified path of the error log file.
   
   
7. Now move the files manually to the new location.
8. Start the SQL Services.

Moving model and msdb databases

Moving of model and msdb databases also follow the similar procedure as moving the tempdb database but with some additional steps.
Since these are also system databases, unfortunately we cannot move them just by detach and attach process, as we cannot attach or detach a system database.

Moving model database:

1. First get the list of model database files by using this query
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='model'  
2. Then for each model database file that you need to move, execute statements like below
   Alter Database model modify
   
   file (NAME = 'modeldev' ,
   FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location
   
   
   Alter Database model modify
   file (NAME = 'modellog' ,
   FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
   
3. Stop SQL Services
   
4. Move the files manually to the new location
   
5. Start SQL Services
   
6. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='model'
   

Moving msdb database:

1. First get the list of msdb files by using this query
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'  
2. Then for each msdb database file that you need to move, execute statements like below
   Alter Database msdb modify
   
   file (NAME = 'MSDBData' ,
   FILENAME = 'Drive:\Path\MSDBData.mdf') -- Mention the new location
   
   
   Alter Database msdb modify
   file (NAME = 'MSDBLog' ,
   FILENAME = 'Drive:\Path\MSDBLog.ldf') -- Mention the new location
   
3. Stop SQL Services
4. Move the files manually to the new location
5. Start SQL Services
6. Verify the new Location
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'

If the SQL Server Instance is configured with Database Mail option, then after the msdb movement you will have to verify that the database mail is working fine by sending a test email.

Moving the tempdb database

There are cases when you might want to move tempdb database from an existing drive to a new drive.

1. When the drive is full and you are in a situation where you cannot extend that drive.
2. Move tempdb to a separate drive to increase its performance.

This is a simple process and cannot be done by detaching and attaching the database, as we cannot attach or detach a system database.
Also we need to restart the SQL services.

Here is the process how we can move the tempdb to a new location.

1. First get the list of tempdb files by using this query
select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'
2. Then for each tempdb file that you need to move, execute statements like below

Alter Database tempdb modify
file (NAME = 'tempdev' , 
FILENAME = 'Drive:\Path\tempdb.mdf') -- Mention the new location

Alter Database tempdb modify
file (NAME = 'templog' , 
FILENAME = 'Drive:\Path\templog.ldf') -- Mention the new location

3. Stop SQL Services
4. Start SQL Services
5. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'

Start or Stop SQL Services using SQLCMD

In one of my previous post "SQLCMD Mode - Run your queries against several servers in a single query window.", i had explained on how to connect and execute queries in SQLCMD mode.
Hope that was useful.

In this post i will explaining on how to stop and start SQL Server services using SQLCMD mode.
First of all, Open a new query window and Change the query execution mode to SQLCMD.
Then type the below command in the new query window that is opened in the SQLCMD mode.
1. To start the services, use the below commands
/* Start Services */

!!NET START MSSQL$MSSQLSERVER

!!NET START SQLAGENT$MSSQLSERVER

2. To stop the services, use the below commands
/* Stop Services */

!!NET STOP SQLAGENT$MSSQLSERVER

!!NET STOP MSSQL$MSSQLSERVER

If you are trying to start or stop a default instance, then then will no changes to the above query.
If you are trying to start or stop a named instance, then Replace MSSQLSERVER with your instance name.
For example, if you are trying to start or stop the named instance "SQL2008", then the commands will look as below.

/* Stop Services */

!!NET STOP SQLAGENT$SQL2008

!!NET STOP MSSQL$SQL2008

/* Start Services */

!!NET START MSSQL$SQL2008

!!NET START SQLAGENT$SQL2008