The Books Online topic covers various best practices that you can use to improve the performance of Backup/Restore operations. Optimizing Backup and Restore Performance in SQL Server Consider whether you should implement the changes or apply the fixes and best practices that are discussed in the corresponding articles. Refer to How It Works: What is Restore/Backup Doing? This blog post provides insight into the current stage of backup or restore operations.Ĭheck whether you're experiencing any of the known issues that are listed in the following table. Use the estimate_backup_restore script to get an estimate of backup times. For more information, see SQL Server, Backup Device Object. You can measure backup and restore throughput information by using the "Device throughput Bytes/sec" and "Backup/Restore throughput/sec" performance monitor counters. You can use the percent_complete column of sys.dm_exec_requests to track the progress of in-flight backup and restore operations. In SQL Server 2016 and later versions, you can use XEvent backup_restore_progress_trace to track the progress of backup and restore operations.
The following is a sample output from the error log: RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec) You can use these details to estimate the time that's required to back up and restore the database in its current state. The SQL Server error log contains information about previous backup and restore operations. If you suspect that the backup operations are either stopped or taking too long to finish, you can use one or more of the following methods to estimate the time for completion or to track the progress of a backup or restore operation:
Backup/Restore throughput depends on how well the underlying I/O subsystem is optimized to handle the I/O volume. Original product version: SQL Server Original KB number: 224071 Backup and restore operations take a long timeīackup and restore operations are I/O intensive. This article provides solutions for common issues that you might experience during Microsoft SQL Server backup and restore operations, and provides references to further information about these operations.
0 kommentar(er)
