Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Changing the default SQL Server backup folder

MSSQLTips author Greg Robidoux By:   |   Read Comments (23)   |   Related Tips: More > Backup

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:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',
  
@value_name='BackupDirectory',
  
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory

This will provide the following output, since we changed the value above directly in the registry.

If we want to change this back to the default folder we can use the following command

EXEC master..xp_regwrite
     
@rootkey='HKEY_LOCAL_MACHINE',
     
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',
     
@value_name='BackupDirectory',
     
@type='REG_SZ',
     
@value='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup'

If we run this command again we can see that this has changed:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',
  
@value_name='BackupDirectory',
  
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory

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.

Next Steps

  • 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.
  • Take a look at these other backup tips


Last Update: 9/15/2008


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, September 15, 2008 - 11:42:21 AM - SAinCA Read The Tip

The tip is useful for 2005+ - is there an equivalent for SS2K, please?


Monday, September 15, 2008 - 6:02:25 PM - aprato Read The Tip

 There's a similar screen in SQL 2000

In Enterprise Manager, right-click on the server name and choose Properties and then the Database Settings tab


Monday, September 15, 2008 - 6:06:16 PM - SAinCA Read The Tip

Yep - known...

How does one CHANGE the default backup destination folder?  There doesn't appear to be an equivalent registry area for 2K...  Poster is NOT a DBA, hence the seemingly "basic" nature of the question...

AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?


Wednesday, September 17, 2008 - 6:21:36 PM - aprato Read The Tip

Do you have a BackupDirectory key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer ? 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 Read The Tip

[quote user="SAinCA"]

How does one CHANGE the default backup destination folder?  There doesn't appear to be an equivalent registry area for 2K.

[/quote]

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).

[quote user="SAinCA"]

AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?

[/quote]

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 Read The Tip

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.

 

Thanks


Wednesday, September 24, 2008 - 9:44:36 AM - SAinCA Read The Tip

Thanks, Mike, it had to be "something different" for 2K.  Will give it a whirl on our test box...


Wednesday, September 24, 2008 - 9:48:20 AM - SAinCA Read The Tip

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.


Tuesday, February 02, 2010 - 9:15:27 AM - admin Read The Tip

For SQL Server 2000 it is basically the same thing. 

Here is an example of the key path for a SQL 2000 installation 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2000\MSSQLServer

The parameter is called "BackupDirectory"


Friday, April 08, 2011 - 4:58:34 PM - Sam Read The Tip

This worked great for new databases that are created.  However, the default location for databases that existed before the registry change still defaults to the old location.  Is there a fix for that?


Friday, April 08, 2011 - 5:06:31 PM - Greg Robidoux Read The Tip

Sam are you saying that for newer databases the backups go to the new backup location, but for older databases the backups go to the older backup folder? Just want to make sure I understand the need.


Friday, April 08, 2011 - 6:09:24 PM - Sam Read The Tip

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 Read The Tip

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'


Saturday, June 16, 2012 - 12:08:32 AM - Ahmet Read The Tip

Thank you. With the help of this tip I solved my "The BackupDirectory registry key is not configured correctly..." problem.


Monday, August 20, 2012 - 11:43:56 AM - Ankit Shah Read The Tip

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.








 


Thursday, October 04, 2012 - 4:24:00 PM - RobM Read The Tip

Also worth a mention is the fact you get a chance to set the dafault backup directory during the installation process.


Thursday, October 04, 2012 - 5:30:41 PM - Greg Robidoux Read The Tip

This tip should be updated.  In SQL 2000 you were able to do this and then in SQL 2005 they removed this option.  But now it is back again as you mentioned using Facets.


Friday, January 18, 2013 - 4:19:35 PM - Eric Read The Tip

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

 

------------------------------

\\server\backuplocation

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 Read The Tip

@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.

Sorry I don't have an answer for you.


Thursday, January 02, 2014 - 12:28:07 PM - Omar Read The Tip

For Windows 64 Bits the regedit key "BackupDirectory" is under:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer


Wednesday, January 29, 2014 - 2:09:46 PM - Pete Read The Tip

Or try this: 

http://www.mytechmantra.com/LearnSQLServer/Changing_Default_Backup_Location.html


Saturday, March 01, 2014 - 8:12:48 PM - Alexander Read The Tip

Greg, you are awesome! The solution of restoring currupted Backup folder path is brilliant!


Tuesday, June 10, 2014 - 12:33:09 PM - Rick Sheeley Read The Tip

I found I also needed to change the following key/value under SQL server 2008R2:

 

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

Value: BackupDirectory

 

Just FYI.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.