3

I just flinched and closed out of habit while waiting for the restore to reach 100%. Did I just break everything? Is it possible to view the progress of the restore?

Update: I was in a bit of a panic, so I roped in my team lead. The database was now stuck in the restoring state and that script didn't work so we just dropped it and started over. It went okay.

Was that necessary? In the UI for a restore there is a period of time before the percentage complete is displayed. I had closed the application before that... not sure if that matters.

  • Do you mean that you killed the connection running the restore command? – Martin Smith Dec 21 '17 at 22:29
  • Yes that's correct. – kaleoh Dec 25 '17 at 5:22
4

This is baked into sp_Whoisactive as well without needing an ad-hoc script.

It's the percent_complete column, over to the right (removed some superflous columns like sql_text and waitinfo):

enter image description here

3

The backup will continue to run to completion.

This code will tell you an approximate time to completion of the backup.

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')
1
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time  
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a  
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.