patternsqlMinor
Estimating the best restore time from saved backups in SQL Server
Viewed 0 times
thebackupsestimatingsqltimeserverfromsavedrestorebest
Problem
I am looking for guidance and any advice on how to estimate the restore time for SQL Server databases once copied locally to the server.
This is just to layout a strategy what will happen if in future my SQL Server gets compromised. We are on to the point where SQL Server is made available and it's time to restore those databases. Assuming backups are valid, I am trying to find a way if we can actually tell or do a best estimation to give timelines for those backups to get restored from backups.
I've found RTOWorstCaseMinutes in https://www.brentozar.com/archive/2021/12/check-your-sql-server-backup-performance-safety-with-sp_blitzbackups/ but not sure if it really means what I am looking for.
If there is a better way on approaching this please suggest.
This is just to layout a strategy what will happen if in future my SQL Server gets compromised. We are on to the point where SQL Server is made available and it's time to restore those databases. Assuming backups are valid, I am trying to find a way if we can actually tell or do a best estimation to give timelines for those backups to get restored from backups.
I've found RTOWorstCaseMinutes in https://www.brentozar.com/archive/2021/12/check-your-sql-server-backup-performance-safety-with-sp_blitzbackups/ but not sure if it really means what I am looking for.
If there is a better way on approaching this please suggest.
Solution
If you use SSMS to restore a backup, it gives a progress bar with percentage complete, which you can roughly estimate the overall runtime from. For example:
Referenced from SQL Server Best Practices: Monitoring Backup and Restore Progress
Alternatively, I've generally found Adam Machanic's
Finally, if you script the restore of your backups instead of using the SSMS Restore Database UI, the Messages tab will also give you updated information on the restore progress.
Regarding your follow-up clarification in the comments, I don't think it's possible to accurately predict how long a restore will take in practice before actually running it since it'll be dependent on the hardware behind the server you're restoring to. This will mostly be bound by the I/O of the disk, but also the CPU will be relevant for decompressing the backup, and of course the overall busyness of the server at the time of the restore comes into play as well.
Your best bet is to routinely restore your backups (which you should be doing anyway to verify they're not corrupt) and create a baseline of how long they're normally taking to restore.
Referenced from SQL Server Best Practices: Monitoring Backup and Restore Progress
Alternatively, I've generally found Adam Machanic's
sp_WhoIsActive is able to pull the progress metrics from the appropriate system catalog entities for backup operations. It gives an estimated time remaining column for such operations in addition to the total runtime so far.Finally, if you script the restore of your backups instead of using the SSMS Restore Database UI, the Messages tab will also give you updated information on the restore progress.
Regarding your follow-up clarification in the comments, I don't think it's possible to accurately predict how long a restore will take in practice before actually running it since it'll be dependent on the hardware behind the server you're restoring to. This will mostly be bound by the I/O of the disk, but also the CPU will be relevant for decompressing the backup, and of course the overall busyness of the server at the time of the restore comes into play as well.
Your best bet is to routinely restore your backups (which you should be doing anyway to verify they're not corrupt) and create a baseline of how long they're normally taking to restore.
Context
StackExchange Database Administrators Q#307949, answer score: 2
Revisions (0)
No revisions yet.