Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

SQL Server Default Backup Directory

Someone asked me in a webinar how to change the default backup directory. I knew, but realized that I didn’t have a reference and ended up with more explanation than needed if I’d had a post. So here it is:

If you right click on a server in Management studio and select properties, you get dialog with lots of options.

serverpropertoes

 

If you click on the “Database Settings” you get this:

serverdbsettings

Note that there is a default path for data files and log files, not not one for backup files.

serverdbsettings1

So how do you change it? In XP or Win 7, click Start and type this right away “regedt32”

regedior

That will start the registry editor. You ought to get a UAC box to confirm access, which is fine. Do that and you’ll be in the registry. In the left pane, browse to this path:

HKey_LocalMachine\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer”

Note that the “MSSQL.1” might be different, depending on your instance. For me it’s SSQL10.MSSQLServer.

backupdir

Note that there’s a “BackupDirectory” key here. You can double click it to change the path:

backupdir2

I changed mine to a new path, c:\sqlbackup. Note that I had to create this folder.

change path

That’s not enough, however. If you go into your adminstrative tools and find the Computer Management and look for Users and Groups (Select groups), you’ll find groups like this. The name varies, depending on the name of your workstation/server and instance.

security

 

Once you have this name, I’d copy it and go to your new folder. Right click, select properties, and then the security tab

sevc1

As you can see, my group isn’t in here, but this group, with the SQL Server service account in it, needs permissions to this folder. So add them, with modify.

 

sec2

Is my default changed? I could now run this:

backup1

and I find a file in my new default folder:

backup2


Filed under: Blog Tagged: Backup/Recovery, sql server, syndicated, T-SQL

Comments

Posted by Jerry Hung on 9 June 2011

To read this directory in T-SQL

DECLARE    @BackupDirectory varchar(1000)

-- get the default backup directory

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',@BackupDirectory OUTPUT ;

Also, remember to restart SQL service after adding folder permission, sometimes it won't work right after

Posted by James Lean on 10 June 2011

You can also do this without having to manually edit the registry.

If you right-click on the server and go to Facets, there is a property under the Server facet called BackupDirectory, which you can use to set the default backup location.

Leave a Comment

Please register or log in to leave a comment.