SQL Backup - 7.2
Backup verification - SQL Backup
This topic describes how to use SQL Backup to set up backup verification for your database backups.
What is backup verification?
The most reliable test of your backups is 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.
SQL Backup 7 allows you to fully verify your backup files with the following steps:
- Create a scheduled backup job to back up databases regularly.
- Test backup files when they are created by including CHECKSUM and RESTORE VERIFYONLY in backup jobs.
- Create a reminder to set up backup verification with a scheduled restore job. The Schedule Restore Jobs wizard will be populated with details from the backup job when you launch it from the reminder.
- Create a scheduled restore job to regularly restore the database from the latest backup.
- Include a database integrity check (DBCC CHECKDB) in the scheduled restore job to fully verify the backup.
- Optionally drop the database automatically as part of the scheduled restore job.
- View results of backup verification in the Activity History.
Example: Verifying the latest backup identified by a folder and file name pattern
This example describes how to set up backup verification for scheduled full, differential and log backups of a database, where each backup file name is unique. In this example we will temporarily restore a test database from the backups in order to verify that the backups can be used.
- Using the Schedule Backup Jobs wizard, on step 1 select the server the database is on, then on step 2 select the types of backup and the database to back up. In this example we will take full, differential and transaction log backups of a database called Sales.
- On step 3, create a backup schedule for each type of backup. In this example, we will take a daily full backup at 00.00, a daily differential backup at 12.00 and transaction log backups every hour.
- On step 4, specify the location in which the backups will be created. You can include the <DATABASE> tag in the folder path to organise your backups into folders according to the database you are backing up.
The backup files will be named automatically because we are scheduling more than one type of backup (full, differential and log); the <AUTO> tag uses the file name format specified in the server's file management options. By default, the file name format is set to create unique file names by including the backup type, instance, database name, and date and time of creation.
- On step 5, specify the compression and optimization settings according to your setup and encrypt the backup if required.
- On step 6, select CHECKSUM and VERIFY to check the backup file when it is created. CHECKSUM will validate any page checksums and generate a backup checksum. RESTORE VERIFYONLY will check the backup is complete and readable, and validate the checksums.
Select Create reminder to create a reminder to schedule a restore job to verify the backups in the Reminders tab. When you launch the Schedule Restore Jobs wizard from the Reminders tab, the wizard is populated with details from the backup job.
Select Send email notification to receive an email with a copy of the completion log. By default, email notifications are only sent if errors occur during the backup process, such as a checksum failing; you can change this to Error or warning or Any outcome.
- Complete the wizard 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.
- When you're ready to create a scheduled restore job to verify your backups, go to the Reminders tab and find the reminder for the Sales database. Click Schedule a restore now to launch the Schedule Restore Jobs wizard populated with details from the scheduled backup job.
- Step 1 of the Schedule Restore Jobs wizard is populated with details from the backup job.
- The destination server (the server the backups will be restored on) is 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 Restore from option is 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 Source database name is populated with Sales, the name of the database being backed up by the scheduled backup job.
- Latest backup set is selected because we scheduled full, differential and log backups. The most recent set of backups available will be restored each time the scheduled restore job runs.
- Folder is populated with the location specified in the Schedule Backup Jobs wizard.
- File name pattern is populated with the default file name pattern, *.sqb. SQL Backup will use the file name pattern to identify the most recent set of backups of the source database in the specified folder.
If we had stored the backups in multiple folders, each folder would be listed with the default file name pattern. SQL Backup would search all specified folders to identify the most recent backups.
- On step 2, Create new database is selected by default and populated with the name of the source database followed by _Verification. In this example it is Sales_Verification.
The Overwrite on subsequent restores check box is selected to ensure that subsequent restores will not fail because a database of the same name already exists.
- On step 3, Individual files to specified locations is selected by default. The grid is populated with details from the source database, using the logical names of the database files at the time the reminder was created. The new database name, such as Sales_Verification is appended to the file name.
- On step 4, Run database integrity check following restore is selected by default.
Select any additional options you want to include in the database integrity check. You can use these to specify the amount of feedback returned and the intensity of the check.
If you want to free the space used by the database between restore jobs, you can select Drop database following restore and specify either On success or Always. If you do not drop the database, it will be overwritten on the next restore because Overwrite on subsequent restores was selected in step 2 of the wizard.
- On step 5, specify the recovery completion state.
Select Send email notification to receive an email 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; you can change this to Error or warning or Any outcome.
- On step 6, create a restore schedule. SQL Backup will identify the latest backups of the database and restore each backup in the set in the correct order. In this example we set the schedule for full backups to 00.00, differential backups to 12.00 and transaction log backups to hourly. In order to verify the full and differential backups and a number of the transaction log backups every day, we set the restore schedule to 18.30 daily. If the disaster recovery requirements called for backups to be verified more frequently, we could set the restore schedule to multiple times a day.
- Complete the wizard to create the scheduled restore job. The reminder is removed from the Reminders tab and the job is added to the Jobs tab. The results of the restore and database integrity check will be displayed in the Activity History.
If you have restored a test database from a backup in order to verify the backup, you may wish to drop the database on completion of the process. The Schedule Restore Jobs wizard gives you the option to drop the database either if the restore and database integrity check were successful (On success), or regardless of the outcome (Always). You can also include these options using the RESTORE command.
Restore to a different server
You can restore backups to a different server using the Schedule Restore Jobs wizard. You may want to do this if you have limited space or resource on the server that the database is running on. For more information, see Backing up and restoring on a network share.
Verify the original database
You can also use the backup verification options to offload verification of a production database to a non-production server. Instead of regularly running a database integrity check on a production server, schedule a backup job for the source database, and then schedule the restore job on a non-production server with a database integrity check.
Additional database integrity check options
You can append the following additional options to the database integrity check (DBCC CHECKDB) to specify the amount of feedback returned and the intensity of the check. You can include these options using the Schedule Restore Jobs wizard or the RESTORE command.
- PHYSICAL_ONLY (Not available if DATA_PURITY or EXTENDED_LOGICAL_CHECKS is selected.) Limits the check to the physical structure of the database, reducing the time required to complete the check. The contents of the check depends on the version of SQL Server you're using; refer to your SQL Server documentation for more information.
- DATA_PURITY (Not available if PHYSICAL_ONLY is selected.) Adds a check for invalid or out-of-range column values to the integrity check. If the database was created on SQL Server 2005 or later, column values will be checked by default and there is no need to include this option.
- EXTENDED_LOGICAL_CHECKS (SQL Server 2008 and later. Not available if PHYSICAL_ONLY is selected.) Adds logical checks on indexes to the database integrity check. Running these checks can have a significant impact on performance and increase the time required to complete the check.
- TABLOCK Limits access to the database while the database integrity check is running. Selecting this option reduces the time required to complete the integrity check.
- ALL_ERRORMSGS Displays all reported errors for each object and includes them in the log file.
- NO_INFOMSGS Turns off reporting for all informational messages. If no additional options are selected, NO_INFOMSGS is included by default as there will not be any informational messages to return.
Refer to your SQL Server documentation for more information about the DBCC CHECKDB command and additional options.
Was this article helpful?
Thanks for your feedback!
- Backups stop working when the SQL account passwords are changed
- Backups and restores taking longer each time they run
- Improving performance by leveraging multi-processor hardware
- Checking the status of a backup or restore
- SQL Backup Agent doesn't start on named instance
- No backup files are erased when the verify option is also used
- Log shipping initialisation failing with error 3176
- Installing SQL Backup unattended
- Issues faced when upgrading an MSCS cluster server
- Cannot schedule log shipping on case-sensitive servers
- Activation fails because response does not match registration properties
- SQL error -1 on backup with 64-bit
- Could not find procedure 'master...sqbutility'
- 'Does not exist' error manually creating a generic service resource
- Backing up log shipping standby databases
- Stopping a backup or restore whilst in progress
- Backing up full-text catalogs using SQL Backup
- Cannot resolve collation conflict
- Microsoft cluster installation (version 4)
- Informing the SQL Agent of job failures
- Database log files may continue to grow even though transaction logs are backed up
- Cannot load xp_sqlbackup.dll
- Minimum Windows Rights Required for SQL Backup Agent
- Bringing a log shipping standby database online
- Console does not support editing the command you selected
- Malformed database disk image error
- Cannot use a local account to run SQL Backup Agent on cluster
- Preparing SQL Servers for remote installation
- SQL error 18210 and VDI error 1010
- Log shipping jobs not deleting old .sqb files
- Scheduled backups may not include new databases
- VDI error 1010 due to an abort request
- Cannot save or see backup templates
- Not all databases appear in the log shipping wizard
- No mapping between account names and security IDs was done
- SQL error 3101 (database is in use) during log shipping
- Insufficient quota to complete the requested service
- Configuring SQL Server procedure memory
- Backing up and restoring on SQL Server Express Edition
- Cannot run two database backups at the same time
- Restoring database backups across a local network
- In some situations the last compressed data block fails to be written to disk
- Configuring permissions for the SQL Backup Agent service
- Restoring backups from one server to a second server using the user interface
- Using SQL Backup tags to automate backup file naming
- VDI 1010 Error
- SQL Backup 5 cannot connect to servers running SQL Backup 4.0
- Files are created when NOWRITE or NOCOMPRESSWRITE is used in conjunction with multiple threads
- Cannot backup a database when full-text catalogs are not online
- Moving a full-text catalog on RESTORE
- Preventing backups over the network from timing out
- Deleting backup history manually
- SQL Backup installation from SQL Toolbelt installer only installs the console
- Console taking a long time to start up
- Reporting across multiple servers, login fails for user NT AUTHORITY\ANONYMOUS LOGON
- SQL Backup Agent cannot start due to account lookup error
- IO Error on backup or restore restart-checkpoint file
- Unable to edit SQL Backup Scheduled Jobs via the Jobs Tab in the SQL Backup GUI (version 5)
- VDI error 1000 caused by improperly installed VDI interface
- VDI Error 1000 caused by account lookup failure
- VDI error 1000 caused by insufficient permissions
- Error 193: %1 is not a valid Win32 application.
- Installing SQL Backup on a cluster
- Restoring the SQL Server MASTER database
- VDI Error 1010: Failed to get the configuration from the server because the timeout interval has elapsed
- Changing the default data location used by SQL Backup
- Index was outside the bounds of the array error during registration
- Repairing the SQL Backup server-side data store
- Troubleshooting slow backup and restore tasks
- Completed backup jobs showing "in progress" status (version 5)
- Restore wizard: files incorrectly labeled 'Missing' when browsing for a backup file to restore.
- When creating a backup job using the wizards some of the user databases may not be listed
- How to restore a filegroup
- System error 1453 while backing up (Insufficient quota to complete the requested service)
- SQL Backup VDI 1030: Failed to create VirtualServiceSet component
- Backups fail on named instances on custom ports
- SQL error 3101 (database is in use) during full database restore
- Error acquiring mutex occurring during use
- Transaction log continually growing on disaster recovery database
- Reseeding a disaster recovery database
- SMTP mail options not being saved
- SQL Backup Activation NullReferenceException Error
- Scheduling SQL Backup restore jobs
- SQL Backup Error 880: permission denied in database
- Deleting remote backup files after a specified time period (version 5)
- SQL Backup Unable to Login to Perform Backup or Restore
- Restore operation errors with exitcode 680
- "Failed to initialize local data store" error
- Trouble browsing network shares when backing up or restoring
- System error 32 (The process cannot access the file because it is being used by another process)
- Log backup failing because there is no current database backup
- Error locating server or instance of SQL Server
- How to set up log shipping between machines on different domains
- SQL Backup and CPU affinity
- SQL Backup mirrored backups
- Error trying to run backup job
- "Access to the database file is not allowed" error
- The server principal NT AUTHORITY\SYSTEM is not able to access the database
- Browsing while using SQL Server authentication is disabled on the selected server
- Cannot generate SSPI context message
- Log ship to multiple servers
- File browser does not work for remote servers
- Configuring Log Shipping to two Target Databases
- Previous backups not deleted according to ERASEFILES setting
- SQL Backup Reporting fails after upgrading to Version 6 from Version 5 for Multiple
- VDI error 1010 error on log backup when no backup is available
- SQL Backup failed with exit code: 1010 SQL error code: 3101 [SQLSTATE 42000] (Error 50000). The step failed.
- Log backup for a database failing to get copied to the log shipping share
- Mirrorfile backup jobs producing corrupt files
- Activity monitor showing sqlbackup status as wait type MSQL_XP
- Backup and restore processes using high percentage of CPU resources
- Warning 485: File does not exist encountered during a restore (but the process completes successfully)
- Log Shipping error 130 when moving restored file
- SQL Backup "Warning 110" generated
- Attempting to restore data into a SQL Express database that exceeds limit
- SQL error 3241 when restoring database
- A nonrecoverable I/O error encountered on Backup or restore operation
- SQL error 15404
- General log-in problem: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
- Warning: System error 1450 when Backing up
- Problems with "Auto-Close" option set when backing up
- Warning: System error 64 (The specified network name is no longer available) on backing up across a network
- SQL error 4208 encountered on backing up
- Warning 170: "Log files are not in sequence" - on log shipping (restore) operation
- Warning: System error 121 (The semaphore timeout period has expired)
- Changing your participation in the quality improvement program
- Installing SQL Backup server components unattended
- Changing your participation in the quality improvement program
- Verifying backups of the master database
- Log files
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
- Getting help offline
- Using SQL Backup to back up to a network share
- Manually installing SQL Backup server components on clusters (Windows Server 2003)
- Manually installing SQL Backup server components on clusters (Windows Server 2008)
- Using SQL Backup log shipping to maintain a standby server
- SQL Backup release notes - version 6.xx
- SQL Backup release notes - version 7.xx
- Working with the new features in SQL Backup 6
- Using flexible licensing with SQL Backup Pro and SQL HyperBac
- Using SQL Backup Pro and SQL HyperBac together on the same server
all SQL products
- Red Gate product acknowledgements
- Activating your products
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs