I just exited SSMS while restoring a UAT database, am I screwed?

Question

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.

score 4 · Answer 1 · answered Dec 21 '17 at 17:24

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):

answered Dec 21 '17 at 17:24

Kris Gruttemeyer

3,879
2
23
43

Robert Rubinberg · Answer 2 · 2017-12-21T17:07:52.623

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')

score 1 · Answer 3 · edited Dec 21 '17 at 17:03

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/

I just exited SSMS while restoring a UAT database, am I screwed?

3 Answers3