Changing the Default Location for New SQL Databases

Problem Statement

Software like Wonderware’s Application Server or InTouch Alarm DB Manager will not ask where to create their databases, they will use SQL’s default location. SQL Server by default will store new databases to the drive where SQL Server has been installed. This can be a problem if the installation drive has limited space, or if policies require new databases to be stored elsewhere. Note: This only changes the location for new databases, and will not affect databases which already exist.

Solution Details

Tested with SQL Server 2005 and SQL Server 2008

This solution involves modifying settings within SQL Server Management Studio. As such, you may require administrator assistance.

Solution Steps:

• On the SQL Server computer, open the SQL Server Management Studio by going to Start > Programs > Microsoft SQL Server and selecting the Management Studio option.
• If prompted for a username and password, enter an administrative user’s credentials.
• Once logged in, under the Object Explorer tree you should see your server listed. Right click on it and select Properties.

• Under Server Properties, go to the Database Settings page. On this page, you can now change the default locations for your databases and log files.

• Once the user presses OK to save the changes, any new databases created will go to this new location.