LiteSpeed Restore from Remote Share v8.0

In versions of LiteSpeed for SQL Server prior to 8.0 I’ve always been able to restore backups from remote shares by cutting/pasting the remote share path (\\serverName\driveLetter$\backups).  In fact, the remote shares are not always actual shares.  If they were LiteSpeed would pick them up in the “browser”.  Often times they are just unshared paths but I’m able to access them using administrator rights.

After upgrading LiteSpeed to 8.0 I discovered that my remote paths will no longer be recognized as a valid path when I manually enter the paths.  The obvious work around is to copy the backup file(s) from the remote server to a local drive.  For small database backups this may be fine.  But for backup files that are hundreds of megabytes in size, the copy alone can take hours.

For DEV and UAT (user acceptance test, aka TEST) machines most of the restores will be coming from a production server.  Basically, once in a while our developers will want a refresh of the existing DEV/UAT databases from the production server.  This is where the issue of restoring from remote shares is the most common.

Instead of copying huge backup files from the remote servers I found it was much easier to change the default path of the local servers (DEV/UAT) backup path to point to the remote server’s backup directories.

Unfortunately, there is no interface in SSMS that will allow us to change the default backup path; it is a registry setting.  We have two ways to change the registry setting:

  1. RegEdit
  2. Extended procedure xp_regwrite

This post will describe how to use the extended stored procedures to change the default backup directory, and then use LiteSpeed to restore a DEV/UAT database from a remote production server.

I did find it necessary to use regedit  to determine the path of the BackupDirectory key.  It seems to be a little different for each instance, depending on the instance name.  The path will be something close to the following:

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

RegEdit

Copy the full path by right clicking on MSSQLServer ==> Copy Key Name.  You’ll need it for the call to the extended procedure to change the path value.

Paste the key from regedit into the following xp command to determine the current default backup directory:

DECLARE    @BackupDirectory VARCHAR(255)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
    @key=’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer’,
    @value_name=’BackupDirectory’,
    @BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory

This will return the current default backup path.  To change the default backup path to the remote server’s backup directory:

EXEC master..xp_regwrite
     @rootkey=’HKEY_LOCAL_MACHINE’,
     @key=’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer’,
     @value_name=’BackupDirectory’,
     @type=’REG_SZ’,
     @value=’\\myRemoteServer\j$\Backups’;

Now when you restore a database in LiteSpeed 8.0 the “Backup Directories” will expand to the directory structure on the remote server.  Just click through the directories as needed and select your backup files (FULL + DIFF for each stripe) and restore as usual.

LiteSpeedRestore

Note: This is only necessary if the backup directory is not explicitly shared.  Shared drives can be found by selecting the Browse Network.  It may take a little more effort to find the specific server, but the share will be presented.

Thanks to Greg Robidoux for his post Changing the default SQL Server backup folder.

If you do decide to make this change be sure to write LOCAL backups to the local backup drive.  By now your backups should already be configured in LiteSpeed, but this may be a point of confusion if a new person is introduced to your backup process.

Stay curious; keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>