Advertisements
Home > SQL 2005 > Default Backup Path in SQL 2005

Default Backup Path in SQL 2005

If you have changed your backup path from default location (i.e. the location SQL was set to backup when it was installed) to a different location there is a small registry setting you should change or you might run into issues in the Maintenance Plans as in my example.

For example:

Create new Maintenance Plan:

  • Create a new Maintenance Plan with Back Up Database Task.
  • Set it to backup to new location; save and close out.

Modify the Maintenance Plan:

  • Open the Maintenance Plan that we just created.
  • Modify the Backup Up Database Task.
  • Change the list of database to backup; click OK.
  • Notice; that the Backup Location changes to a different location then what you had entered.

I made a similar change and saved it without checking the backup location so next day my backup failed. Because the default location no longer existed on the server.

Resolution:

Install SP2+CU9; but since SP3 is avalaible installing that would be best idea. Seems to resolve the issue; it seems it does not read the value from the registery when I modify the Maintenance plans now.

But I would still recommend the following steps because lets say you execute the following code:

BACKUP DATABASE TestDB
TO DISK = 'TestDB.bak'
GO

This will cause the backup to go to default location again instead of the proper backup location (i.e. your new default location).

Go to following key in Registry and put the new path in …

First we need to determine the instance Name; go to
[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\]

Note down the value for the default instance usually MSSQL.1.

Now go to …
[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\]

And change the value for BackupDirectory to the new value.

Now if you modify a Maintenance Plan it will grab the new value. You don’t need to restart server or services.

**NOTE: Do not delete it and do not set it to nothing; as it will not be able to modify the Maintenance Plan without that value**

Update June 12, 2009
Please take a small cautious about this; as GUI interface will return error if you change it to UNC path, Link.

Advertisements
Categories: SQL 2005
  1. December 30, 2010 at 9:21 pm

    This is fine and works to some extent but I have had issues with this. Is there a way to change the default backup location from the get go while maintaining the application install location on the C:\ drive? For example if you want all backups to occur on D:\ drive while application resides on C:\ drive? Just a hypothetical sittuation.

  2. December 30, 2010 at 9:34 pm

    In SQL Server 2005 no; you have to do it manually. Or you must supply the backup path in the 'BACKUP DATABASE [DBNAME] TO DISK='D:\dbname.bak'.In SQL Server 2008 install, you are now able to enter a proper backup location from get go.

  1. No trackbacks yet.

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

%d bloggers like this: