Troubleshooting SQL Server backup and restore operations

Applies to: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Developer Edition

Reference topics for SQL Server backup and restore operations

  • For extensive information about backup and restore operations, see the following topics in Books Online.
    • Back Up and Restore of SQL Server Databases: This topic covers the concepts of the backup and restore operations for SQL Server databases, provides links to additional topics, and provides detailed procedures about how to perform various backup or restore tasks (such as verifying backups, backing up by using T-SQL or SSMS, and so forth). This is the parent topic about this subject in Books Online. You can navigation through the topic list for related topics and subtopics. The navigation in the topic list
  • The following table lists additional topics that you may want to review for specific tasks that are related to backup and restore operations.

    ReferenceCan provide answers for
    2027537 Best Practice recommendations for SQL Server Database Backups The best practice recommendations for working on database backups.
    BACKUP (Transact-SQL)Provides answers to basic questions that are related to backup. Provides examples of different kinds of backup and restore operations.
    Backup Devices (SQL Server)Provides a great reference for understanding various backup devices, backing up to a network share and related tasks.
    Recovery models (SQL Server)Covers in detail the various recovery models: Simple, Full, and Bulk-Logged. Proviodes information about how the recovery model affects backups.
    Back Up and Restore of System Databases (SQL Server)Covers strategies, and discusses what you must be aware of when you work on backup and restore operations of system databases.
    Restore and Recovery Overview (SQL Server)Covers how the recovery models affect restore operations. You should review this if you have questions about how the recovery model of a database can affect the restore process.
    Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)Various considerations that you should be aware of when a database is moved or you encounter any issues that affect logins, encryption, replication, permissions, and so on.
    Working with Transaction Log BackupsPresents concepts about how to back up and restore (apply) transaction logs in the full and bulk-logged recovery models. Explains how to take routine backups of transaction logs (log backups) to recover data.

    Hint In SQL Server 2005 and the later versions, you can back up the log while any full backup is running.
    SQL Server Managed Backup to Windows AzureA new feature that was introduced in SQL Server 2014 that automates backups by offering support for point-in-time restore operations.

Scenarios of SQL Server backup and restore operation issues

  • The SQL Server error log contains information about past backup and restore operations. You can use these details to estimate the time that is required to back up and restore the database in its current state. The following is a sample output from the error log:

    RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec) 
  • SQL Server 2016 Preview introduces a new XEvent "backup_restore_progress_trace" that you can use to track the progress of backup and restore operations.
  • You can also use the percent_complete column of sys.dm_exec_requests to track the progress of in-flight backup and restore operations.
  • The throughput information that is related to backup and restore operations can be measured by using the Device throughput Bytes/sec and Backup/Restore throughput/sec performance monitor counters.
  • How to query the progress of backup process currently running in SQL Server
  • How It Works: What is Restore/Backup Doing? This blog post can help you gain insight into the current stage of backup or restore operations.

Things to check

  1. Check whether you are experiencing any of the known issues that are listed in the following table. Consider whether you should implement the changes or apply the fixes and best practices that are discussed in the corresponding articles.

    Knowledge Base or Books Online linkExplanation and recommended actions
    Optimizing Backup and Restore Performance in SQL ServerThe Books Online topic covers various best practices that you can use to improve the performance of Backup/Restore operations. For example, you can assign the SE_MANAGE_VOLUME_NAME special privilege to the Windows account that is running SQL Server to enable instant initialization of data files. This can produce significant performance gains.
    2920151 Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters

    2822241 Windows 8 and Windows Server 2012 update rollup: April 2013
    Current system rollups can include fixes for known issues at the system level that can cause performance issues that affect programs such as SQL Server. Installing these updates can help prevent these issues.
    2878182 FIX: User mode processes in an application are unresponsive on servers that are running Windows Server 2012

    Backup operations are I/O intensive and can be affected by this bug. Apply this fix to help prevent these issues.
    309422 How to choose antivirus software to run on computers that are running SQL Server Antivirus software may hold locks on .bak files. This can affect the performance of backup and restore operations. Follow the guidance in this article to exclude backup files from virus scans.
    2820470 Delayed error message when you try to access a shared folder that no longer exists in Windows 8, Windows 8.1, Windows Server 2012, or Windows Server 2012 R2
    967351 A heavily fragmented file in an NTFS volume may not grow beyond a certain size
    304101 Backup program is unsuccessful when you back up a large system volume
    2455009 FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2 The presence of many virtual log files could affect the time that is required to restore a database. This is especially true during the recovery phase of the restore operation. For information about other possible issues that can be caused by the presence of many VLFs refer to KB 2028436 .
    A backup or restore operation to a network location is slowIsolate the issue to the network by trying to copy a similarly sized file to the network location from the server that is running SQL Server. Verify the performance.
  2. Check for more error messages in the SQL Server error log and Windows event log for more pointers about the cause of the problem.
  3. If you are either using third-party software or database maintenance plans for performing multiple backups at the same time, consider whether you should change the schedules so that there is less contention on the drive to which the backups are being written.
  4. Work with your windows administrator to check for firmware updates for your hardware.