Walkthrough: Database backup, restore, and verification

This walkthrough explores the new features in SQL Backup Pro 7. It covers:

Contents:

Schedule a backup

Using the reminders

Schedule a restore

Schedule a backup job

Step 1: Launch the Schedule Backup Jobs wizard

Launch the Schedule Backup Jobs wizard

To schedule a backup of our database we launch the Schedule Backup Jobs wizard from the button on the toolbar.

By choosing the Schedule Backup Jobs wizard (rather than the Back Up wizard) we will have the option to create a reminder to verify our backups.

Step 2: Choose to perform a new backup or use previously saved settings

Selecting the server to back up

We now select the server from which we wish to back up the database. We can also choose to use settings from a template that we can customize and configure. In this case, we're going to create a new backup.

Step 3: Choose the database and the type of backup to create

Choosing the backup and the type of backup to create

Next, we choose the type of backup we want to create and select the database we want to back up. If full backups have previously been made, we can decide to perform differential or transaction log backups. In this walkthrough, we will create a full database backup.

Step 4: Create a backup schedule

Create a backup schedule

Here, we can create a schedule for our backups. In this walkthrough, we're creating a full backup and we're scheduling it to occur every weekday at 18.00. We could opt for more frequent backups to suit our needs, and can decide whether we want this schedule to run for a finite period, or run indefinitely.

Step 5: Configure backup file settings

Configuring backup file settings

In this step, we decide whether to back up to a single file, a single file mirrored to a second location, or split the backup over multiple, smaller files. Splitting the backup across multiple files can reduce the time needed to create it.

Here, we can also specify the file name of the backup or have it named automatically.

If we need to purge older backups as part of the process, we can choose to overwrite existing files or delete existing backup files by age or quantity.

If we want to copy the backup to a network location, we can also configure to which folder the backup should go and whether to purge existing backups at that location.

Step 6: Configure compression, encryption, network resilience and optimization settings for the backup

Configuring compression,network resilience and encryption settings

Next, we can select one of four compression levels for our backup. If we are unsure which compression level to use, the Compression Analyzer helps us by sampling data to suggest the optimal compression level for our database.

Here, we can also encrypt our data. With SQL Backup Pro, we can choose between 128-bit and 256-bit AES encryption.

SQL Backup Pro lets us optimize the performance of our backup by selecting the number of multiple threads to be used for the backup process. We can choose a maximum transfer and data block size for memory and disk optimization.

We can also protect our backup process against network outages which can interrupt backup operations and cause job failure. SQL Backup Pro is designed to minimize the disruption caused by network outages by automatically continuing the data transfer once the connection is re-established. By default, SQL Backup Pro is set to retry connecting to the network after 30 seconds and up to 10 times.

Step 7: Configure backup checks, reminders and email notification

Configure backup checks and email notification

This new step gives us the option to test backup files when they are created, by including CHECKSUM and RESTORE VERIFYONLY in the backup jobs.

CHECKSUM will validate any page checksums and generate a backup checksum. RESTORE VERIFYONLY will check the backup is complete and readable, and will validate the checksums.

For Jobs which include a full backup, we can also select 'Create reminder' which adds a reminder to schedule a restore job, to fully verify the backups. If we launch the Schedule Restore Jobs wizard from the Reminders tab, the wizard will be populated with details from the backup job. This will be demonstrated later in this walkthrough.

We can choose to be notified by email, with a copy of the completion log. By default, email notifications are only sent if errors occur during the backup process, such as the checksum failing if an error occurs. We can modify the email settings and choose to receive an email only when there are errors or warnings, or be informed about any outcome.

Step 8: Review a summary of the options chosen and complete

Review a summary of the options chosen

Before we confirm the backup schedule we can review a summary of all the options we've chosen, and view the SQL script for use with the command line utility or stored procedure. We then click Finish to create the scheduled backup job.

The job is listed in the Jobs tab and a reminder is added to the Reminders tab. The results of the CHECKSUM and RESTORE VERIFYONLY will be recorded in the Activity History.

Using the reminders

Step 9: How to set up a reminder

We can create a reminder to verify our backups when we create a scheduled full backup job using the Schedule Backup Jobs wizard (see step 7 above). A reminder is created for each database included in the scheduled backup job and can be found in the Reminders tab.

Step 10: The Reminders tab

The Reminders tab

The Reminders tab displays reminders to set up backup verification for each of our database backups. We can use SQL Backup Pro to verify our backups by creating a scheduled restore job to regularly restore a database from the latest backup and run a database integrity check (DBCC CHECKDB) to verify the logical and physical integrity of all its objects.

When we're ready to create a scheduled restore job, we go to the Reminders tab, find the reminder for the database we want to verify, and click Schedule a restore now. Launching the Schedule Restore Jobs wizard in this way will ensure that the wizard is populated with details from the scheduled backup job.

Schedule a restore job

Step 11: Launch the Schedule Restore Job wizard

In this walkthrough we will launch the Schedule Restore Job wizard from the Reminders tab, so the steps of the wizard will be populated with details from the backup.

NB: If we want to schedule a backup restore, but haven't come through the Reminders tab, we can simply launch the wizard from the SQL Backup Pro user interface. If we launch the wizard like this, it will not open pre-populated with any details of a scheduled backup.

Step 12: Select the destination server and backups to restore

Select the destination server and backups to restore

In this first step, we will find the destination server set to the server on which the scheduled backup job was created. For information on scheduling restore jobs to a different server, see Backing up and restoring on a network share.

The rest of the wizard will also be populated with details from the backup job. As we're setting up a regular restore schedule the 'Restore from' will be set to 'folder and file name pattern'; because each backup will be created with a unique file name, it is not possible to specify the exact backups to restore each time.

The folder, file name pattern, source database name, and the type of backup we want SQL Backup Pro to look for when it performs the scheduled restore, will already be filled in for our convenience.

Step 13: Choose whether to overwrite an existing database or restore the backup to a new database

Choose whether to overwrite an existing database or restore to a new database

In this step, we decide whether to overwrite an existing database, or whether to create a new database. 'Create new' is selected by default, and the new database name is pre-populated with the name of the source database followed by _Verification, though we can change this to any name that suits us.

We'll find that the checkbox 'Overwrite on subsequent restores' is also selected by default. We want to choose this option because we're creating a schedule for regular restores, and this will ensure that subsequent restores will not fail because a database of the same name already exists.

Step 14: Choose where to restore the database files to

Choose where to restore the database files to

Here, we can choose the file locations for the restored database. We have the option to restore 'All files to default locations', 'Data and log files to specific locations', or restore 'Individual files to specific locations'.

Again, we'll find the grid pre-populated with details from the source database, including the logical names of the database files at the time the reminder was created. We could change the 'restore to' file location and also add or remove files from the database to restore if we needed to.

Step 15: Specify the verification options for the restored database

Specify the verification options for the restored database

In step 4 we can set the level of verification we want to run on the backup(s).

We want to make sure that the database integrity check (DBCC CHECKDB) is selected, as this will fully verify the backup. We can also choose from any additional options we want to include in the database integrity check. We can opt for a more or less intense check and we can specify the amount of feedback we want returned.

Step 16: Select options for dropping the database following restore

Also in step 4 of the Schedule Restore Job wizard, we can select the option to 'Drop database following a restore'. This is really useful if we want to free the space used by the database between restore jobs.

We can choose to 'Always' drop the database, or to drop it only 'On success'. If we choose not to drop the database at all, it will be overwritten on the next restore because we selected 'Overwrite on subsequent restores' in step 2 of the wizard.

Step 17: Configure restore options for the destination database

Configure restore options for the destination database

Here we can select whether we want the recovery completion state of the restore to be 'Operational' or 'Read only'.

We can also choose to receive an email notification with a copy of the completion log, including any errors which arise during the database integrity check. By default, email notifications are only sent if errors occur during the backup process, such as the checksum failing if an error occurs. We can modify the email settings and choose to receive an email only when there are errors or warnings, or be informed about any outcome.

Step 18: Create the restore schedule

Create the restore schedule

This is where we can create the schedule for our restores.

We can set the restore schedule around the schedule we have created for our backups, and have complete control over the time and frequency of the restores, to suit our disaster recovery requirements.

We can schedule a restore job to verify backups even if the backups themselves do not yet exist; at the time of the restore SQL Backup Pro will automatically identify the latest backup of the database and restore that. If we had scheduled a set of full, differential, and transaction log backups of a database, SQL Backup Pro will restore each backup in the set in the correct order.

Step 19: Review a summary of the scheduled restore and complete

Review a summary of the scheduled restore and complete

Before we confirm the restore schedule we can review a summary of all the options we've chosen, and view the SQL script for use with the command line utility or stored procedure. We click 'Finish' to complete the wizard and create the scheduled restore job. The reminder is removed from the Reminders tab and the job is added to the Jobs tab.

Step 20: Review the verification results

Review the verification results

Once the restore has run, we can view the results of the restore and database integrity check in the Activity History tab, alongside the results of any backups that may have been performed. To view any of these items in more detail, we would simply click on it, and a detailed summary will pop up.

And that's it! If you would like to see how your backup, restore, and verification processes can be strengthened with SQL Backup Pro, try it now with a free, fully functional and supported 14-day evaluation.

Pricing
$995 per server
SQL Backup Pro is licensed and priced per server, with volume discounts available.

Got a question?
Download

Try a 14-day fully functional free trial of SQL Backup Pro.

SQL DBA Bundle
SQL DBA BundleSeven essential tools for database administration.


Contains:

Find out more:

Awards
Community choice gold - Best backup and recovery product