How to clear most recently used (MRU) servers list

As the days move with your work on SQL Server management studio, it will accumulate the list of servers that you have been working on, starting from the point of installation of  SQL Server management studio.
This list is called as MRU servers list. Here MRU refers to Most Recently Used.
This makes your life easier by showing the most recently used servers by you and you can just select one from the list instead of remembering the server name and typing it manually.

As the list becomes big or the servers that you were using are no more used by you or for any other reasons you may want to clean up this list, Here is the process on how to clear the MRU servers list.

For SQL Server 2005

1. Make sure that the "SQL Server management studio" is not opened
2. Go to "Run"
3. Type this command %APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\ and click "ok". This will open up the folder where this list is stored
4. Now search for the file "mru.dat" and rename this file.
5. After renaming, launch the SQL Server management studio and you will not see any of the most recently used servers in the list.

For SQL Server 2008

1. Make sure that the "SQL Server management studio" is not opened
2. Go to "Run"
3. Type this command %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\ and click "ok". This will open up the folder where this list is stored
4. Now search for the file "SqlStudio.bin" and rename this file.
5. After renaming, launch the SQL Server management studio and you will not see any of the most recently used servers in the list.
   

DTS packages in SQL Server 2005 and SQL Server 2008

As you all know that the DTS packages in SQL server 2000 is replaced by the giant called Integration Services which has more features than DTS, and DTS is no more supported in SQL Server 2005 and up.
To give a backward compatibility until the DTS packages are migrated(or converted by you) to SSIS packages, you can import the DTS packages into SQL Server 2005 and SQL Server 2008 and call them in the jobs to do their work.

These Imported packages will be present at the below location in SQL Server management Studio.
SQLServer>> Management>> Legacy>> Data Transformation Services.

The DTS packages under this path cannot be edited in SQL Server 2005 and SQL Server 2008.
For editing these packages, you need to install "DTS Designer Components"

If you want to edit DTS packages in SQL Server 2005 go here, download and install the component "SQLServer2005_DTS.msi"

If you want to edit DTS packages in SQL Server 2008 go here, download and install the component "SQLServer2005_BC.msi"

Once you install the DTS Designer Components, it will allow you to edit the DTS packages as you do in SQL Server 2000.
Just right-click on the package and choose the option "open" and this will open up the DTS designer for you.

I strongly recommend to convert the DTS packages to the SSIS packages as the support to DTS will be completed removed in future versions of SQL Server.

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.