This walkthrough exploring the new features in SQL Backup Pro 7 covers:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
