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.