Latest version: 7.2
Notes & articles
Working with the new features in SQL Backup 6
SQL Backup 6 introduces many new features designed to make your backup and restore routines even more efficient and reliable. If you are already familiar with SQL Backup 5, this article will help you get up to speed quickly with some of the new features in SQL Backup 6.
If you have not used SQL Backup before, there are a number of walk-throughs and demo videos available that will introduce you to SQL Backup's main features. For more detailed information, the Learning SQL Backup topics available in the SQL Backup Support Center explain how to use all the features of SQL Backup 6.
This article assumes that you have already installed or upgraded both the SQL Backup graphical user interface (GUI) and the SQL Backup server components. Read more about installing and upgrading.
- Network resilience (SQL Backup Pro 6 and later)
- Compression level 4 (SQL Backup Pro 6 and later)
- Kill existing connections (SQL Backup Pro 6 and later)
- Log shipping: prevent destination database initialization (SQL Backup Pro 6 and later)
- Enhanced time-line responsiveness
- Re-runnable wizards
- Manage existing backup files with new delete option
- View SQL Backup log files from the SQL Backup GUI
- Check for orphaned users during restore (SQL Backup Pro 6 and later)
Network resilience (SQL Backup Pro 6 and later)
SQL Backup Pro 6 introduces network resilience to protect your backup and log shipping jobs from network outages. The network resilience features of SQL Backup Pro 6 and later are turned on by default; you don't need to do anything to start taking advantage of these features immediately.
Network resilience for backups and backup copies
If SQL Backup encounters a read/write error while writing or copying a backup file, it will wait for 30 seconds, then attempt to continue the operation. SQL Backup will wait and retry up to 10 times in total; if the operation has not completed successfully after 10 retries, SQL Backup will mark the operation as failed.
You can use the SQL Backup wizards to change the default network resilience settings (wait for 30 seconds; retry up to 10 times). These settings apply to ad-hoc backups, scheduled backup jobs, and for the backup and copying stages of log shipping.
If you are using the SQL Backup command line or extended stored procedure, you can change the network resilience settings with the new keywords
DISKRETRYINTERVAL. If you omit these keywords, the default values are used automatically. To stop network resilience retries, specify
See The BACKUP command for more information.
Extended network resilience for log shipping
To make your log shipping jobs less susceptible to prolonged network outages, SQL Backup uses an extra level of network resilience whenever you are copying transaction log backup files.
Each transaction log backup file that needs to be copied is placed in a queue (the Log Copy Queue). The copy process for each file in the queue may result in network resilience retries (described above). During a prolonged network outage, the maximum number of retries may be reached, causing the copy process to fail. When this happens, the file remains on the Log Copy Queue. The copy process is then reattempted after two minutes, and then regularly for up to 24 hours.
You can modify the copy schedule for files on the Log Copy Queue by creating registry settings on the server that is performing the backup and copy operations, and then restarting the SQL Backup Agent service.
If you are using the SQL Backup command line or extended stored procedure, you can disable extended network resilience for log shipping by specifying the new
USESIMPLECOPY argument. See The BACKUP command for more information.
You cannot disable extended network resilience from the SQL Backup GUI.
Compression level 4 (SQL Backup Pro 6 and later)
SQL Backup Pro 6 adds an extra compression level to those available for backups using SQL Backup 5. The existing compression levels are unchanged; the new compression level 4 provides higher compression ratios than compression level 3, at the expense of more CPU-cycles used. Read more about compression levels.
Compression level 4 will be of benefit whenever you are short of storage space. Additionally, if you are transferring data across the network, compressing it using compression level 4 will reduce the amount of data to be transferred, and hence the time required for the transfer. This may be of particular benefit for log shipping, where you are transferring a sequence of transaction log backup files to a network share at frequent intervals.
The Compression Analyzer includes analysis for compression level 4, and is now available from the Tools > Utilities menu in SQL Backup, as well as from the wizard pages listed above.
If you are using the SQL Backup command line or extended stored procedure, you can specify
COMPRESSION=4. See The BACKUP Command for more information.
Kill existing connections (SQL Backup Pro 6 and later)
Sometimes it is necessary to kill all existing user connections to a database. For example, if you need to restore a backup over an existing database, or if you need to back up the transaction log tail for a database (that is, using
WITH NORECOVERY or
STANDBY). If you attempt either operation while there are existing connections to the database, the operation will fail.
SQL Backup Pro 6 and later helps you to avoid failures caused by existing user connections by killing these connections automatically as part of the associated SQL Backup process.
Note that if there are uncommitted transactions associated with an existing connection, these transactions will be rolled back automatically before the connection is killed. The time required to roll back an uncommitted transaction is usually similar to the amount of time that the transaction had been running for prior to rollback.
If you are using the SQL Backup command line or extended stored procedure, you can kill existing connections to a database by specifying the new keyword
Log shipping: prevent destination database initialization (SQL Backup Pro 6 and later)
SQL Backup Pro provides the Log Shipping wizard for you to set up log shipping between two databases.
In SQL Backup Pro 5, initialization of the log shipping destination database is performed automatically. Initialization involves backing up the source database and restoring it to the destination database, which is left in
NO RECOVERY or
In SQL Backup Pro 6 and later, if your destination database is already in the correct state, you can prevent initialization of the destination database by clearing the check box in Step 1 of the Log Shipping wizard.
Enhanced timeline responsiveness
In SQL Backup 6, the timeline display is very similar to the time line in SQL Backup 5. However, changes to the way that SQL Backup collects and refreshes the data mean that the timeline in SQL Backup 6 refreshes more quickly, and is more responsive when you have added many instances and databases.
The timeline display is now refreshed every minute, starting with the selected SQL Server instance, then other instances that are visible. While the connection to the instance is being refreshed, is displayed. You can continue to interact with the timeline while a connection is being refreshed.
Red shading has been added to show that the connection to a SQL Server instance has failed. Green shading shows that data is being collected for that particular section of the timeline:
In SQL Backup 6, the step-by-step wizards (Back Up, Schedule backup jobs, Restore, and Log Shipping) all include a Retry button on the final progress dialog box. Retry is available if an activity fails to complete:
When you click Retry, you are returned to the final wizard page, and have the option to step back and adjust any of the wizard settings before attempting to run the activity again.
Manage existing backup files with new delete option
SQL Backup 6 includes a new option to help you manage existing backup files. In SQL Backup 6, you can specify the number of recent backup files that you want to keep; SQL Backup will delete older files.
Additionally, you can now use the SQL Backup GUI to specify delete options for backup files written to network locations.
If you are using the SQL Backup command line or extended stored procedure, you can delete files based on the number of recent files that you want to keep, by using the existing keywords
ERASEFILES_REMOTE. These keywords now accept values of the form
b indicates the number of recent files to keep. For example,
ERASEFILES_REMOTE=5b ensures the latest 5 remote files are kept; older remote files are deleted. The files are deleted only if the backup process completes successfully.
View SQL Backup log files from the SQL Backup GUI
You can now more easily view the contents of SQL Backup log files for specific activities.
Right-click a completed activity on the time line, or an item in the Activity History tab or Log Copy Queue tab, and click Show Log to display the contents of the log file:
You can also show the log file contents from the Properties window for an activity, by clicking Show Log.
Check for orphaned users during restore (SQL Backup Pro 6 and later)
If you are restoring a database to a different SQL Server instance from the backup source, it is possible that some of the restored database user accounts will not have an associated server login on that instance. These database user accounts are known as orphaned users, and will be unable to provide access to the database unless you create their associated logins.
SQL Backup Pro 6 can run a check for orphaned users once a restore has completed. If any orphaned users are found, a warning is included in the SQL Backup log file along with a list of the orphaned users.
SQL Backup does not fix orphaned users automatically. If orphaned users are reported, you should consider creating the required logins on the SQL Server instance that you are restoring to.
For more information about database user accounts, logins, and fixing orphaned users, refer to your SQL Server documentation.
You can specify the option to check for orphaned users from step 3 of the Restore wizard.
If you are using the SQL Backup command line or extended stored procedure, you can check for orphaned users when restoring a database by specifying the new keyword
See The RESTORE command for more information.
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