2

On my server a database is currently restoring according to the SQL Server Management Studio

In the history

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'myDB'
ORDER BY restore_date DESC

I can't see any entry of that restore currently running.

Is there any other system-table where I can find out who started the restore and when?

2
  • What makes you think that the database is currently in the middle of a restore? I'm guessing you're just looking at Object Explorer (SSMS) and seeing the text "Restoring..." right next to your database? Is that correct? Nov 27, 2013 at 14:23
  • I am looking in SQL Management Studio. It sais "Restoring" and accessing the database (via sql management studio or code) fails because of restore
    – Ole Albers
    Nov 28, 2013 at 9:37

1 Answer 1

1

To see the restore progress, use below t-sql

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

Is there any other system-table where I can find out who started the restore and when?

You can find out the restore history - especially the user_name will give you who restored the database.

SELECT  [rs].[destination_database_name], 
[rs].[restore_date], 
[bs].[backup_start_date], 
[bs].[user_name],
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name], 
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf 
ON [bs].[media_set_id] = [bmf].[media_set_id] 
--where [rs].[destination_database_name] like '%database_name%' -- OPTIONAL FILTER
ORDER BY [rs].[restore_date] DESC
2
  • Thanks. The first one looks nice. To the second part: I already selected rows of the restorehistory (as mentionend in the question). It looks like ACTIVE restores are just not in there
    – Ole Albers
    Nov 28, 2013 at 9:35
  • 1
    The first one gives you active restores and second one gives you restore history.
    – Kin Shah
    Nov 28, 2013 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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