Two ways to change the default backup directory for SQL Server

This has driven me nuts for years.

I always change the default data directory in SQL Server Management Studio. That’s easy to do and the feature to do so is easily discoverable. Just right-click the server instance in the Object Explorer, and go to Properties. Then change it as per the screen shot below.

(This image looks crap because it is scaled. The dialog doesn’t allow resizing any smaller, so I had to scale it or have the image overlay the sidebar on the right of this blog. Clicking the image will open it with the correct size in a new tab.)

image

I like all my databases to be in C:\Data. No digging through directories. But it gives me two really annoying problems:

  1. The default location for backups is somewhere else, and I’d really like them to be here too, in the same directory as the data. So I have to browse to the correct directory whenever I make a backup.
  2. When I restore a backup, I want to overwrite the database. But when I select a backup to restore and go to the Options tab, one of the two files (the .LDF file) always defaults to the wrong file name – without a suffix of “_1”, even though I have the right data and log directories preselected and the log file already exists. So I have to manually type in the right file name or browse for it every time I restore a backup. This is painful.

I hoped that changing the default backup directory would fix this odd behaviour. (It does!) But how do you change this setting?

Method One

This setting to do so isn’t discoverable. I couldn’t even find it, and neither could the person who wrote this tip. Their solution, which is the one I used, is to change the registry setting, using regedit.exe, for the value found at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer

Here’s what my system’s registry looks like:

SNAGHTML6d886e19

Note that the key will be slightly different depending on the version of SQL server that you have installed. I’m running SQL Server Express 2008 R2, 64-bit, which corresponds with the sub-string MSSQL10_50.SQLEXPRESS. The rest of the key stays the same so it shouldn’t be difficult to find what key applies to your particular version.

Method Two

But it turns out there is a user interface to change this. Right-click on the server instance in Object Explorer and then clicks Facets. Then you can change the BackupDirectory value in the user interface, as indicated in the screen shot below. I don’t know why they didn’t put this setting together with the data and log directories, because they really need to be together to avoid the odd behaviour of the log file name being wrong when restoring backups.

image

Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Database, Work and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s