Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  Thomas Stringer Nov 27 '13 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 '13 at 9:37

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
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 '13 at 9:35
1  
The first one gives you active restores and second one gives you restore history. –  Kin Nov 28 '13 at 13:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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