Problem When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases, but you can not change the default directory for your backups. Is it possible to change the default directory for backups, so it does not need to be specified each time I run a backup?
Solution The directories for the default data files, log files and backups are stored in the system registry. As mentioned already you have the ability to change the default data and log directories using SQL Server Management Studio, by right clicking on the server name and selecting properties and navigating to the Database Settings page as shown below.
But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory. To find this we need to look in the registry.
If we open the registry using REGEDIT or some other tool and if you navigate to this key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer or similar for your instance of SQL Server we can see the following information The highlighted line below shows the default directory for the backups for this one instance.
If you want to change the value you can edit the registry value and save the new location. So for this example let's change it to 'D:\SQLBackups'.
If I do a backup using SQL Management Studio and click on "Add" for a new destination file the following screen shows the default folder has now changed to 'D:\SQLBackups'.
Another way to change the registry is to using the extended stored procedures XP_REGREAD and XP_REGWRITE.
To read the value from the registry you can issue the following command:
To determine where SQL Server is installed you can right click on the server name, select Properties. The root directory as highlighted below will show you the corresponding install name for the instance such as "MSSQL2" highlighted below, so you know which registry entry needs to be changed.
That's all there is to it, so save yourself some time and change the default location.
If you are tired of changing the location for your backups each time you do a backup you can make this one time registry change and not have to select a the folder each time. In addition, this works the same if you use the T-SQL BACKUP commands. You will not need to specify the folder just the file name and the backups will work without a problem.
Do you have a BackupDirectory key under
I don't have access to SQL Server 2000 at the moment and I'll have to install it to check the installed reg keys
Wednesday, September 24, 2008 - 4:42:06 AM - MikeHinds
How does one CHANGE the default backup destination folder? There doesn't appear to be an equivalent registry area for 2K.
Create ONE Backup device using Enterprise Manager, which will then use your chosen backup folder as the default (one of many useful behaviors that went away in 2005).
AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?
I've done this on a cluster, but can't verify fully that this is the case without actually failing one over to test. Not knowing where the default is stored: if in the registry, you may need to do it twice; but if it's stored in Master or Msdb, then seems like you're covered.
Wednesday, September 24, 2008 - 5:04:50 AM - psivakumar
Is there a way to point to a remote server's shared folder(s)? This will make backup to remote servers and keep a copy standing by for better recoverability. Currently, I am backing up in the local server (SQL) and then copying to remote server.
Wednesday, September 24, 2008 - 9:44:36 AM - SAinCA
To do what you want, I'd recommend using Red-Gate's SQL Backup as it runs exceptionally quickly, including compression (HUGE disk saving), and has a simple "copy to network drive" option you can employ. The guys here tried local and network-copy using native commands but it became a pain - they were glad of SQL Backup's feature... Hope you can justify the minimal expense for what the tool delivers.
Correct. I'm able to change the location and remove the old location, but every time I right click-->all tasks-->back up, the default location would revert back to what it was before I made the change.
Saturday, April 09, 2011 - 8:08:55 AM - Greg Robidoux
Hi Sam, I just tried this on SQL Server 2008 and the GUI showed the new location for me after I made the change. I also had done a backup for this database prior to changing the location and it still showed the new location using the GUI.
I am not sure why it is not working consistently for you.
You could also try this to see if the backup for one of these databases goes to the new location and put in your database name and the name of the file for the backup file without specifying a backup location.
BACKUP DATABASE dbName TO DISK = 'fileName'
BACKUP DATABASE master TO DISK = 'master_test.bak'
More Easiest way to change it for sql 2008 and later versions....
Using SQL Server Management Studio, connect to SQL Server Database Engine.
In Object Explorer right click the SQL Server Database Engine Instance and choose Facets from the popup menu as shown in the snippet below.
View Facets select Facet as Server Settings and change the value of BackupDirectory Facet Property to the New Folder Location where you want to save your database backups in future and click OK to save the changes.
Once you have updated the SQL Server Backup Folder location go ahead and restart the SQL Server Services for the new values to come into effect.
This has worked for me; however, there are issues when I go to restore my backups. When I select restore -> from device, and try to select a source, I get the error
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.
If I manually enter the entire share path and filename into the 'Locate backup file' dialog that appears afterwards, it works and is able to restore the file - however, this is a large pain in the rear. Why is it not able to see / browse from the default location in the first place? Can this be resolved, or is the slow & roundabout way our only option?
Thursday, January 24, 2013 - 10:53:41 AM - Greg Robidoux
@Eric - I am not sure how to resolve this one. I tested this on SQL 2008 R2 and get the same error that you reference. For some reason SQL is only looking at the local drives and does not want to use the UNC path.