SQL DBA Blog

Tag: MSSQL SPIDs

Get Estimated Backup Restore Time in SQL Server

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.

August 18, 2022
Get Last Database Restore DateTimes in SQL Server

This post contains a script to help you get the last dates and times for when your databases were last restored in SQL Server.

The script below queries the M.S.D.B database sys.databases and restorehistory tables. You should get into the habit of searching MS Docs for every system table you query in SQL Server. This gives you the reference point you need for documentation on columns and query examples.

If your database was not created by a RESTORE procedure then the restore_date column value will be NULL. For example, if you’ve installed SQL Server and created a new database, then this script may not be of much use.

The above restore_type column shows ‘D’ to indicate a Full backup restore has happened. ‘L’ would indicate a transaction log restore, and ‘I’ is a differential restore. The column meanings can be found in the MS table docs as linked above in this post.

If you’re interested in reading more about querying SQL Server currently running processes, have a look at my MSSQL SPIDs blog tag, or have a look at my MS SQL DBA Blog Posts for random tips from a DBA.

July 23, 2022
Get Estimated Database Restore Time
If you’re backing up or restoring a large database in SQL Server, you can run the SQL script in this post to find out how long it has left to complete. The SQL script will get an estimated database restore date/time & percentage completed so far.

The performance of a backup or restore in MSSQL will depend on many things. For example, your server hardware, backup options, or user/client queries running in the background.

The estimated time returned by the query below is based on the performance at the time of execution.
```
-- get estimated db restore time sql
-- works in sql server 2022
SELECT 
    command,
    s.text,
    start_time,
    percent_complete,
    CAST(((DATEDIFF(s,start_time,GETDATE()))/3600) AS VARCHAR) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GETDATE())%3600)/60 AS VARCHAR) + 'min, '
        + CAST((DATEDIFF(s,start_time,GETDATE())%60) AS VARCHAR) + ' sec' AS running_time,
    CAST((estimated_completion_time/3600000) AS VARCHAR) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 AS VARCHAR) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 AS VARCHAR) + ' sec' AS est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, GETDATE()) AS est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG', 'DbccSpaceReclaim','DbccFilesCompact')
```
As shown in the screenshot above, the SQL script returns process info for all BACKUP, RESTORE & DBCC commands running right now. It includes the start date-time, SQL command, percent_complete, and estimated completion time.

I’ve seen a multi-terabyte database take 1.5 days to complete a full backup. This information can often be vital for reviewing database backup & restore strategies, or you may want to try to improve backup restore performance.

We can also use sp_whoisactive to get similar SQL process information (excluding est_completion_time) by viewing the percent_complete column in the sys.dm_exec_requests; SQL Server system DMV.
April 9, 2018