When you install Microsoft SQL Server, the system databases are typically located deep within the C:\Program Files directory. However, many database administrators prefer to have their data, including system databases, reside on a different drive than the executables. In this article, we will discuss why you might want to move these databases and provide a script to help you accomplish this task.
Why Move System Databases?
There are several reasons why you might want to move the system databases:
- Organizational preference: Having all your databases, including system databases, in an easily accessible directory structure can make management and troubleshooting easier.
- Improved performance and protection: By storing your databases on a RAID array or SAN separate from the boot disk, you can benefit from enhanced performance and data protection.
- Quick recovery: If you lose your boot disk, having the system databases on a separate location allows you to reinstall the OS and SQL Server, and quickly restore your databases without any data loss.
Moving the System Databases
Moving the system databases, including the master and mssqlsystemresource databases, can be a non-trivial and potentially scary task. However, with the right steps and precautions, it can be done safely. Here is an overview of the steps involved:
- Use the ALTER DATABASE MODIFY FILE command to specify the new location for the system databases.
- Update the service startup parameters in the registry to point to the new location of the master database and log.
- Stop the SQL Server service to unlock the files.
- Move the database files to the new location.
- Start the SQL Server service with the necessary startup and trace flags to prevent automatic recovery.
- Use ALTER DATABASE MODIFY FILE to record the new location of the mssqlsystemresource database.
- Move the mssqlsystemresource file.
- Set the mssqlsystemresource database to read-only.
- Stop and start the SQL Server service in normal mode.
It is important to note that the mssqlsystemresource database must be located in the same directory as the master database. Failure to do so can result in errors and issues.
Using the Script
To simplify the process of moving the system databases, we have provided a script that you can use. The script requires two parameters: the instance name and the full path where the data files will be moved. If you are using the default instance, provide “MSSQLSERVER” as the instance name.
Before running the script, make sure to backup your data files and take a screenshot of the Advanced tab in the SQL Server Configuration Manager. This will allow you to manually restore the registry values if necessary.
It is highly recommended to test the script on a test server or install SQL Server on a workstation to ensure it works as expected before running it on a production server.
Conclusion
Moving the system databases in SQL Server can offer several advantages, including improved organization, performance, and data protection. However, it is important to approach this task with caution and follow the necessary steps to ensure a smooth transition. The script provided in this article can help simplify the process and ensure the system databases are moved correctly.