Wednesday, February 23, 2011

Changing the Default Backup Path in SQL Server 2008 / 2005

One of the issues that I’ve found with SSMS is that if your default backup path is a UNC path and you use the GUI to restore a database, you will receive the following nasty error message when you try to point the GUI to that default UNC path.

“Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. 
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.”

Turns out that the SSMS Locate Backup File window does not support browsing out to a UNC path, but the path can be entered manually in the Selected Path edit box like so: \\server\path and the .bak backup file can be specified in the File Name edit box.

To get rid of this annoying error message, our answer was to change the default backup path for SQL server. While this will not change where we place our current backups, since their backup locations are specified in a script, it will change where SSMS wants to place ad hoc backups in the future. So if you are using the SSMS GUI to do a backup, be aware of where your backup is being placed.

To change the default backup path in SQL Server, you must change the BackupDirectory value in the registry. The key can be found here:

SQL 2008:
HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\MSSQL10.<instance>\MSSQLServer

SQL 2005:
HKey_Local_Machine\Software\Microsoft\Microsoft SQL Server\MSSQL.<instancenumber>\MSSQLServer

Note: Modify the registry at your own risk. Always backup your registry before making any changes.

2 comments:

  1. Thanks Keivin, SQL 2012 gives sesecurityprivilege error but appears to be the same issue. We are backing up to a Datadomain by EMC - I had hoped that SQL 2012 fixed this issue. All backups work fine via script to UNC but the GUID just likes to give nast errors when browsing UNC paths.

    ReplyDelete
  2. To change the default backup path via SSMS: Right-click the database server in the tree. Facets -> BackupDirectory.

    ReplyDelete