I was recently approached by my manager to free up a Direct Attached Storage Device (DAS) from a test server so that it could be leveraged in a higher environment.
To my amazement, both the system databases and binary executables were installed on the drives I needed to move. From the lessons I learnt over the past few days, I am going to write a series of articles on moving a SQL Server system.
Please refer to my database files article which explains how to move user databases.
Today, we are going to concentrate on moving system databases except for master and resource. Below is a description of the system database from MSDN.
[master] – Records all the system-level information for an instance of SQL Server.
[msdb] – Is used by SQL Server Agent for scheduling alerts and jobs.
[model] – Is used as the template (settings) for all databases created on the instance of SQL Server.
[tempdb] – Is a workspace for holding temporary objects or intermediate result sets.
[resource] – Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
[distribution] – Stores metadata and history data for all types of replication, and transactions for transactional replication.
This task can be accomplished by using the following steps from MSDN.
1 – Find the location of the current databases
2 – Update databases with the new file locations
3 – Stop all services.
4 – Move files using windows explorer.
5 – Start all services.
6 – Check to see if service broker is enabled on MSDB.
The following script contains TSQL that will do steps 1, 2 and 6. Also, it contains commented out DOS commands to manage services. See my managing services article for steps 3 and 5. Last but not least, everyone should know how to copy data files with Windows Explorer, step 4.
The image below shows the system data files move to a new home.
In summary, be very careful with file locations and names. During one test, I used the wrong name for the log file for MSDB. I ended up with a test system in which I had to recreate MSDB from scratch.
Like any DBA task, make sure you have good and recent backups before playing with the system databases. Next time, I will talk about moving the master and resource databases.
