This post contains a SQL script to get estimated completion times and process information for your currently running database backups & restores in SQL Server.
Backing up a database can often take a very long time. It depends on various factors why a backup can take longer to complete. One more obvious reason is, that the bigger your database gets, the longer it will take to backup & restore.
Other backup performance considerations include whether your server is busy with transactions/queries, or you may be running on old hardware, and I’ve seen having fragmented heaps cause extended backup runtimes too.
Get Estimated Database Backup or Restore Time MSSQL
When you’re waiting on a database backup or restore to complete in SQL Server, we can run the script below to get an estimated time of how long the backup has left to go –
We have one 5GB database here currently being backed up at the time of executing the DMV script. The backup progress is at 27% and is due to complete 1 minute after the backup was initiated.
This script will also be useful for getting the full command of the currently running SQL process on your SQL Server Instance, by removing the filter (WHERE Clause) on the query.
Script Gist > https://gist.github.com/petesql/2385ca22d7ee211b045904e34ea082c9