This page describes how to back up to and restore from a network share using SQL Backup Pro. You may want to to store backups on a network share, for example, because you do not have enough disk space to store the backups on a local server. You may want to restore a database from a backup stored on another server, for example, in order to verify the backup on a non-production server.

You can use SQL Backup Pro to:

• Back up a database locally and then copy the backup to a network share
• Back up a database directly to a network share
• Restore a database from backups stored in a network share

SQL Backup Pro's network resilience settings can reduce the impact of network outages when backing up or restoring. If the connection is interrupted during a backup or restore, SQL Backup Pro will attempt the operation again after 30 seconds up to 10 times by default. You can adjust these settings on step 4 of the Back Up wizard or step 5 of the Schedule Backup Jobs wizard or with by using the DISKRETRYCOUNT and DISKRETRYINTERVAL keywords in a BACKUP script run from the command line or extended stored procedure. The network resilience settings used to create the backup are applied when restoring from the backup.

Nevertheless, backing up directly to a network location can be slower than copying the backup after creation and occasionally problematic:

• More resources are required than for a local backup, resulting in increased load on the SQL Server and increased duration for the backup. Transferring compressed data after backing up locally is quicker than writing data across a network.
• By depending on external network resources, backup performance becomes less predictable.

Backing up locally and copying to a network share

It is best practice to back up locally and copy the backup to a network share. To do this, the appropriate permissions must be set up for the network share.

Security and permissions

• The SQL Backup Pro process is controlled by a dedicated SQL Backup Agent application for each SQL Server instance.
• Before you can create a backup and copy it to a network share, you must ensure that the SQL Backup Agent service application permissions are set up appropriately. The user account used to start up the SQL Backup Agent service and connect to the SQL Server must have access to the folder in which the backup is to be created, and it must have BACKUP DATABASE permissions to the SQL Server.
• If you do not want to grant BACKUP DATABASE permissions to the user account used to start up the SQL Backup Agent service, for additional security you can configure a SQL Server authenticated account to connect to the SQL Server. For further information, see Permissions.
• If necessary, you can copy the backup files to a network share that is "locked down". To do this, you will need to set up a different security model. This is also documented in Permissions.

Creating the backup locally

When you have set up the permissions appropriately, use the Back Up wizard or Schedule Backup Jobs wizard to create a backup. On step 3 of the Back Up wizard or step 4 of the Schedule Backup Jobs wizard:

1. Specify a local folder for the backup location.

   

2. Select the Copy backup to network check box and specify a location on the network share. Type the full path, including the server name, for example: \\ServerName\MyFolder\<DATABASE>. Note that the file path is relative to the selected SQL Server. For example, if you have chosen to back up a database on a remote SQL Server instance called ServerA and you specify a local path such as C:\Backups, the backup files will be created on the C: drive on ServerA, not on the local server.

   Alternatively, click to open the Folder Browser. From the Server drop-down list, select the network share. If it is not displayed, click Add Server and specify the server name or IP address. Other servers will only be visible to the local server if it has the appropriate permissions to write to or read from them. The name of the local server you are connected to and your user name are displayed above the Server list. This information may explain why some servers cannot be browsed. 

3. Click Test to check whether you have the necessary permissions to write to the network share.

   

4. Proceed through the rest of the wizard as normal.

When the job is run, SQL Backup Pro will create the backup file or files with compression and encryption as specified, run any backup checks as specified, and then copy the files to the network share.

Backing up directly to a network share

To back up directly to a network share, the account used to start up the SQL Backup Agent service application must have full permissions on the network share address (in addition to the other permissions specified in Permissions).

Security and permissions

The following procedure describes how to grant the permissions using Microsoft Windows 7; refer to your Microsoft Windows documentation for full details for your operating system.

1. In Windows Explorer, right-click on the folder in which you want to create the backups, then click Properties.
2. On the Sharing tab, click Advanced Sharing.
3. In the Advanced Sharing dialog, select Share this folder then click Permissions.
4. In the Permissions dialog, click Add to open the Select Users, Computers or Groups dialog box.
5. Find or type the startup account for the SQL Backup Agent service and grant it full control.
6. Click OK on all of the dialog boxes.

Creating the backup on a network share

When you have set up the permissions appropriately, use the Back Up wizard or Schedule Backup Jobs wizard to create a backup. On step 3 of the Back Up wizard or step 4 of the Schedule Backup Jobs wizard:

1. Specify a location on the network share as the backup location folder. Type the full path, including the server name, for example: \\ServerName\MyFolder\MyFile. Note that the file path is relative to the selected SQL Server. For example, if you have chosen to back up a database on a remote SQL Server instance called ServerA and you specify a local path such as C:\Backups, the backup files will be created on the C: drive on ServerA, not on the local server.

   Alternatively, click to open the Folder Browser. From the Server drop-down list, select the network share. If it is not displayed, click Add Server and specify the server name or IP address. Other servers will only be visible to the local server if it has the appropriate permissions to write to or read from them. The name of the local server you are connected to and your user name are displayed above the Server list. This information may explain why some servers cannot be browsed.

   

2. Leave the Copy backup to network box clear, unless you want to copy the backup files to a different location after they have been created on the network share.
3. Proceed through the rest of the wizard as normal.

Restoring from a network share

You can use SQL Backup Pro to restore a database from a backup file (or set of files) on a different server by either copying the files to a local folder before the restore, or restoring directly from the network location. Note that to restore by creating a new database, the user must have CREATE DATABASE permissions.

Restore wizard

1. Select Browse for backup files to restore from the drop-down list.
2. Select the SQL Server on which you want to restore the database.

3. Click Add Files and browse for the backup files. From the Server drop-down list, select the network share. If the network share is not displayed, click Add Server and specify the server name or IP address. Other servers will only be visible to the local server if it has the appropriate permissions to write to or read from them. The name of the local server you are connected to and your user name are displayed above the Server list. This information may explain why some servers cannot be browsed.

   

Continue through the Restore wizard as normal.

Schedule Restore Jobs wizard

1. From the Select destination server drop-down list, select the server on which you want to restore the database.

2. Select Specific backup files from the drop-down list if you are creating a scheduled restore job for a file of a specific name. Click Add Files to open the File Browser.
   

   Select Folder and file name pattern from the drop-down list if you are creating a scheduled restore job for the most recent backup of the database available each time the job runs. Enter the name of the source database and click Add folder to add a folder and file name pattern. Click  to open the Folder Browser.

   

3. Use the File Browser to browse for a specific file, or the Folder Browser to browse for the location in which the backups will be stored. From the Server drop-down list, select the network share. If the network share is not displayed, click Add Server and specify the server name or IP address. Other servers will only be visible to the local server if it has the appropriate permissions to write to or read from them. The name of the local server you are connected to and your user name are displayed above the Server list. This information may explain why some servers cannot be browsed.
4. Continue through the Schedule Restore Jobs wizard as normal.