SQL Backup
Latest version: 7.2
Knowledge Base
Troubleshooting slow backup and restore tasks
Category: Troubleshooting & error messages
Date: 19 May 2009
Product: SQL Backup
When backing up or restoring a database using SQL Backup, the process may appear to take longer than it should. SQL Backup's own logs may indicate a high throughput for the restore, but the time taken to execute task itself may seem to go for too long.
Database backups can seem to take longer in SQL Backup due to its' own housekeeping tasks, such as deleting old backup history and erasing backup files that are older than the configured retention time. The remainder of this article, however, concerns only time delays caused by SQL Server tasks that must complete before SQL Backup can begin to backup or restore data.
Restoring a database using SQL Backup can cause time-consuming housekeeping and other SQL Server activity to initiate. For instance, when restoring a large database from a full backup, SQL Server needs to allocate the disk space before the restore can even begin. Some backup and restore activities may trigger a SQL Server checkpoint operation. None of these activities is accounted for by SQL Backup; if a lengthy operation needs to be carried out by SQL Server before the server is ready to accept data from SQL Backup's restore task, SQL Backup's status (exec master..sqbstatus) will report 0 bytes restored until SQL Server can accept the backup data stream from SQL Backup.
Fortunately, it is possible to detailed information about backup and restore operations from SQL Server using a trace flag. If you open a new query window in SQL Server Management Studio or Query Analyzer, and connect to the server's MASTER database using an account with a SYSADMIN role, executing the following query will turn on extra logging for the backup and restore operations of the SQL Server.
DBCC TRACEON (3004, 3605, -1)
When it is no longer necessary to obtain detailed information, the extra logging can be turned off again using
DBCC TRACEOFF (3004, 3605, -1)
Once you have executed the trace command, you may start to see the extra logging in the SQL Server activity logs immediately upon the next backup or restore operation. For instance, in Management->SQL Server Logs->Current in SQL Server Management Studio. In the following example log entry where the trace had been enabled, you will see that the creation of the data files for a new database being restored had taken approximately 34 seconds to initialize.
Date,Source,Severity,Message
06/06/2008 12:11:59,spid58,Unknown,RestoreDatabase: Finished
06/06/2008 12:11:59,spid58,Unknown,Restore: Done with MSDB maintenance
06/06/2008 12:11:59,spid58,Unknown,Writing backup history records
06/06/2008 12:11:59,Backup,Unknown,Database was restored: Database: MyBigDatabase<c/> creation date(time): 2007/03/04
(17:02:14)<c/> first LSN: 69877:8282:1<c/> last LSN: 69877:8383:1<c/> number of dump devices: 1<c/> device information:
(FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_BABB2FB2-2F7A-4E0F-B3DB-66D46CF18587'}). Informational message. No user action
required.
06/06/2008 12:11:59,spid58,Unknown,Restore: Writing history records
06/06/2008 12:11:59,spid58,Unknown,Resuming any halted fulltext crawls
06/06/2008 12:11:59,spid58,Unknown,Restore: Database is restarted
06/06/2008 12:11:59,spid58,Unknown,PostRestoreReplicationFixup for MyBigDatabase complete
06/06/2008 12:11:58,spid58,Unknown,PostRestoreReplicationFixup for MyBigDatabase starts
06/06/2008 12:11:58,spid58,Unknown,PostRestoreContainerFixups: fixups complete
06/06/2008 12:11:58,spid58,Unknown,PostRestoreContainerFixups: running fixups on MyBigDatabase
06/06/2008 12:11:56,spid58,Unknown,Recovery is writing a checkpoint in database 'MyBigDatabase' (10). This is an
informational message only. No user action is required.
06/06/2008 12:11:56,spid58,Unknown,Zeroing completed on D:\sql2005\MSSQL.1\MSSQL\Data\MyBigDatabase_LOG.LDF
06/06/2008 12:11:56,spid58,Unknown,Zeroing D:\sql2005\MSSQL.1\MSSQL\Data\MyBigDatabase_LOG.LDF from page 43788 to 43848
(0x15618000 to 0x15690000)
06/06/2008 12:11:56,spid58,Unknown,FixupLogTail() zeroing D:\sql2005\MSSQL.1\MSSQL\Data\MyBigDatabase_LOG.LDF from 0x15617e00
to 0x15618000.
06/06/2008 12:11:54,spid58,Unknown,Starting up database 'MyBigDatabase'.
06/06/2008 12:11:54,spid58,Unknown,Restore: Restarting database for ONLINE
06/06/2008 12:11:54,spid58,Unknown,Restore: Transitioning database to ONLINE
06/06/2008 12:11:54,spid58,Unknown,Restore: Done with fixups
06/06/2008 12:11:54,spid58,Unknown,Rollforward complete on database MyBigDatabase
06/06/2008 12:11:50,spid58,Unknown,Restore-Redo begins on database MyBigDatabase
06/06/2008 12:11:50,spid58,Unknown,The database 'MyBigDatabase' is marked RESTORING and is in a state that does not allow
recovery to be run.
06/06/2008 12:11:49,spid58,Unknown,Starting up database 'MyBigDatabase'.
06/06/2008 12:11:49,spid58,Unknown,Restore: Backup set restored
06/06/2008 12:11:49,spid58,Unknown,Restore: Data transfer complete on MyBigDatabase
06/06/2008 12:11:49,spid58,Unknown,FileHandleCache: 0 files opened. CacheSize: 12
06/06/2008 12:11:49,spid58,Unknown,Restore: LogZero complete
06/06/2008 12:11:49,spid58,Unknown,Restore: Waiting for log zero on MyBigDatabase
06/06/2008 12:09:45,spid58,Unknown,Zeroing completed on D:\sql2005\MSSQL.1\MSSQL\Data\MyBigDatabase_LOG.LDF
06/06/2008 12:09:11,spid58,Unknown,Restore: Transferring data to MyBigDatabase
06/06/2008 12:09:11,spid58,Unknown,Restore: Restoring backup set
06/06/2008 12:09:11,spid58,Unknown,Restore: Containers are ready
06/06/2008 12:09:11,spid58,Unknown,Zeroing D:\sql2005\MSSQL.1\MSSQL\Data\MyBigDatabase_LOG.LDF from page 1 to 132880 (0x2000
to 0x40e20000)
06/06/2008 12:09:11,spid58,Unknown,Restore: PreparingContainers
06/06/2008 12:09:11,spid58,Unknown,Restore: Attached database MyBigDatabase as DBID=10
06/06/2008 12:09:11,spid58,Unknown,Restore: BeginRestore (offline) on MyBigDatabase
06/06/2008 12:09:11,spid58,Unknown,Restore: Planning complete
06/06/2008 12:09:11,spid58,Unknown,Restore: Planning begins
06/06/2008 12:09:11,spid58,Unknown,Restore: Backup set is open
06/06/2008 12:09:11,spid58,Unknown,Restore: Configuration section loaded
06/06/2008 12:09:11,spid58,Unknown,Opening backup set
06/06/2008 12:09:11,spid58,Unknown,RestoreDatabase: Database MyBigDatabase
06/06/2008 12:08:58,spid58,Unknown,Restore: Configuration section loaded
Document ID: KB200806000267 Keywords: SQL,Backup,TRACEON,restore,slow,troubleshoot,hang
Was this article helpful?
Installing or upgrading SQL BackupSecurity model
Activating SQL Backup
Registering SQL Servers with SQL BackupExploring the graphical user interfaceBacking up and restoringTools and utilitiesSettings and optionsScripting SQL BackupWorking with SQL BackupError code reference
Acknowledgements
SQL Backup
- 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
all products
- 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
SQL Backup
- 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
all 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
