Backing up to hosted storage
Published 18 February 2013
The SQL Backup Pro Hosted Storage service will be closing down at the end of 2017. We will assist customers who use the service to migrate to storing backups in Amazon S3.
- SQL Backup Pro 9 includes integration with Amazon S3. This was released in early May 2017.
- The Hosted Storage service has stopped accepting new backups on 14th August 2017. Existing backups will still be available to restore until December 2017.
- The Hosted Storage service will close down and existing backups will be removed at the end of December 2017.
Backing up to hosted storage is only available in SQL Backup Pro 7.3 and later.
What is hosted storage?
Hosted storage is a service provided by Redgate which allows you to store backups created with SQL Backup Pro off-site and access them online.
You can upload a copy of a backup to hosted storage as part of a backup job. Using the SQL Backup Pro Hosted Storage website, you can access your backups whenever you need them.
For more information, see the SQL Backup Pro Hosted Storage website.
In order to upload backups to hosted storage, you need to:
- Create a SQL Backup Pro Hosted Storage account on the SQL Backup Pro Hosted Storage website.
For each SQL Server you want to upload backups from, link the server to your account. You can do this when creating a backup job, on step 3 of the Back Up wizard (step 4 of the Schedule Backup Jobs wizard). Under Hosted storage, click Link Account. Enter your account email address and password and click Link Account.
Alternatively, from the Tools menu, select Server Options. Open the Hosted Storage Settings tab and select the SQL Server that you want to link to your account. Enter your account email address and password and click Link Account.
If you have multiple SQL Server instances, you will need to link each one to an account. This can be the same account, or a different account for each server.
Once your account is linked, you can copy backups to hosted storage using either the SQL Backup Pro wizards, or a SQL Backup Pro script.
Using the wizards
On step 3 of the Back Up wizard (step 4 of the Schedule Back Up Jobs wizard), select Copy backup to hosted storage.
Complete the wizard to create the backup or schedule the job. All other backup options, including copying to a network share, encryption, and compression, are available when backing up to hosted storage.
Once the backup has been written to disk, SQL Backup Pro starts uploading a copy of the backup to hosted storage. See Viewing the progress of uploads to hosted storage below.
Writing a backup script
To copy a backup to hosted storage, add
COPYTO_HOSTED to the
WITH clause of a backup script, and run it from the command line or extended stored procedure.
For example, using the extended stored procedure:
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [pubs] TO DISK = ''C:\Backups\pubs.sqb'' WITH COPYTO_HOSTED " '
Using the command line:
-SQL "BACKUP DATABASE [pubs] TO DISK ='C:\Backups\pubs.sqb' WITH COPYTO_HOSTED
The other backup options in SQL Backup Pro, including
COPYTO = <network location>,
KEYSIZE = 128|256, and
COMPRESSION = 1|2|3|4, can be used in conjunction with
For more details, see The BACKUP command.
If you already have backup jobs set up, you can edit them to copy the backups to hosted storage as part of the job.
- In the SQL Backup Pro GUI, open the Jobs tab and double-click the backup job you want to edit. The Edit Backup Job wizard is displayed.
- Go to step 3 and select Copy backup to hosted storage.
- Continue through the wizard and click Finish to save your changes to the job.
Alternatively, edit the job in SQL Server Management Studio, adding
WITH COPYTO_HOSTED to the SQL Backup job step.
Backing up multiple files
When backing up to hosted storage, you can both split backups into multiple files and back up multiple databases at the same time.
- If you split a backup into multiple files, all files will be written to disk before SQL Backup Pro starts uploading a copy of the first file.
- If you back up multiple databases at the same time, each backup is written in sequence. SQL Backup Pro will start uploading the first backup as soon as it has been written to disk.
For more information on backing up multiple databases, see Backing up all databases on an instance.
Viewing the progress of uploads to hosted storage
Once the backup has been written to the disk location you specified, SQL Backup Pro starts uploading a copy of the backup to your Hosted Storage account.
You can view the status of the upload from the In Progress tab:
- Each file is listed as Pending when it is added to the upload queue.
- Each file is listed as Uploading during upload attempts.
The backup is listed in the Activity History asSuccessful (awaiting copy to hosted storage) while the upload is in progress. Right-click the row and select Properties to view the status of each file. Once the upload has completed, the backup is listed as Successful.
To cancel an upload that is in progress, open the In Progress tab, right-click the row and select Cancel. This will not affect the backup written to local disk. It is not possible to restart an upload after it has been cancelled.
Retrying after a failed upload
If an upload attempt fails, for example because the network connection was interrupted, SQL Backup Pro will attempt to upload the file again at regular intervals. In between attempts, files will be listed in the In Progress tab asPending. For information about the cause of the failure, right-click the row and select Show Log, or in the Activity History, right-click and select Properties.
SQL Backup Pro will continue retrying the upload at increasing intervals for 24 hours.
- Attempt 1: immediately
- Attempt 2: 2 minutes after attempt 1 failed
- Attempt 3: 4 minutes after attempt 2 failed
- Attempt 4: 6 minutes after attempt 3 failed
- Attempt 5: 8 minutes after attempt 4 failed
- All subsequent attempts: 10 minutes after the preceding attempt failed
To prevent any further upload attempts, open the In Progress tab, right-click the upload and select Cancel. This will not affect the backup written to local disk.
If the backup fails to upload after 24 hours, or the upload is cancelled, the backup will display in the Activity History asSuccessful (with warnings). Right-click and select Properties to see details of the problem.
To try uploading again, take a new backup of the database and select Copy backup to hosted storage. Alternatively, use SQBHostedStorageClient.exe to upload the backup manually. For more information, see SQBHostedStorageClient.exe.
For more about the upload schedule and changing the behavior of it, see The In Progress tab.
Managing your backups in hosted storage
You can manage your backups in hosted storage using the SQL Backup Pro Hosted Storage website.
You can view your backups in the My backups page, organised by SQL Server instance and by upload date.
To download a backup file, click the download buttonon the right of the file you want to download.
To delete individual backup files, click the delete buttonon the right.
To delete all backups older than a specified age automatically, set a retention policy on the Backup retention page. The retention period for each file is calculated from the date and time the file was uploaded. When you apply a retention policy, any files older than the retention period are deleted immediately and cannot be recovered. Remaining files are automatically deleted at the end of the retention period.
Restoring a backup from hosted storage
To restore a database from a backup in hosted storage:
- Find the backup you want to restore in the My backups page of the SQL Backup Pro Hosted Storage website.
- In the Actions column, click to download the backup.
In SQL Backup Pro, open the Restore wizard. On step 1, from the drop-down list, select Browse for backup files to restore.
- Click Add Files and select the backup file you downloaded.
- Continue through the wizard to restore the database from the backup file.