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.)
I like all my databases to be in C:\Data. No digging through directories. But it gives me two really annoying problems:
- 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.
- 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?
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:
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.
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.