This walkthrough will show you:
In step one, we specify our source and destination database. We can choose between restoring to an existing database and creating a new database. If we decide to overwrite an existing database, the restore process will fail if there are existing connections to the destination database. To prevent this kind of problem, you can now check a box to kill any existing connections to the database automatically. This means the restore process can be completed successfully without further intervention.
In step two, we choose a backup folder, and if we need to purge older backups as a part of the process, we can delete existing backup files by age or quantity.
In this step, we can also select one of four compression levels to optimize our backups for speed or size. Our in-house testing shows that SQL Backup Pro can compress database backups by up to 95%. This is particularly handy if you have a security requirement to keep a long history of backups.
To optimize the log shipping performance, you can determine the number of threads SQL Backup Pro uses for this operation. Using multiple threads can considerably speed up the backup process.
Here, we can also choose to receive an email notification if an error occurs.
In step three we specify the network share. This is a storage area that both the source and destination servers can access. We can type the name of the network share to which we want to copy the backup files, or we can browse for the folder. We must then test that the source and destination servers have correct permissions to access the network share.
We can also adjust the network resilience settings. Network outages are a common problem when transferring data. They can interrupt backup operations and cause job failure. With network resilience, SQL Backup Pro will reattempt an interrupted operation, making your backup process much more reliable. We can specify here how long SQL Backup Pro should wait and how many times it should retry transferring an individual transaction log.
An extra level of resilience makes log shipping with SQL Backup Pro self-healing. If a prolonged network outage occurs, and the standard number of retries is exceeded, SQL Backup Pro keeps a record of the files that failed to copy and defers the log file transfer for a set period (two minutes by default). The transfer becomes a pending task in the Log Copy Queue tab, and SQL Backup Pro will keep attempting to complete the transfer at regular intervals for a maximum of 24 hours before marking it as failed. Once the network outage is resolved, SQL Backup Pro will transfer all the affected transaction log files, starting with the earliest. Log shipping is then brought right up-to-date, without the need for intervention.
The settings for restoring transaction log backups can be adjusted in step three.
On completion of the restore operation we have to decide whether we want our restored database to be non-operational or read-only, since the database cannot be in an operational state if we want to restore transaction log files.
Once the backup files have been restored, SQL Backup Pro moves the files from the network share so they are not processed during the next run. Here, we specify the folder to which we want these processed backup files moved. We can choose to delete files that are over a certain age or quantity. To be notified of any problems, we can also specify an e-mail address.
Next, we select the schedules for the transaction log backups. We can set the frequency, and here we choose for transaction log backups to occur every day, every 15 minutes between midnight and 1 minute to midnight. We base the restore schedule on the backup schedule, with an offset of 2 minutes. Alternatively, we could create a separate schedule for the restores at this stage by clicking Specify a schedule.
In the final step we review a summary of our log shipping configuration. If we spot any corrections to be made, we can go back through the wizard to make changes. If we are happy with our choices, we click Finish to create the SQL Server Agent jobs that will perform the log shipping.
If you would like to see how you can make your log shipping strategy as robust, reliable and easy to set up as demonstrated here, please try our free, fully functional and supported 14-day evaluation of SQL Backup Pro.
