The last few days I’ve been trying to recover from a pretty big SQL server failure. After running into problems with Microsoft’s support I have had to turn to the web for help here. After all I’m a network guy & only dabble in SQL from time-to-time. I’m posting this to mainly have as a reference for myself but if anyone finds it useful that’s great too. Also note, this is not from me but from others on the ‘net.
Microsoft SQL Server Management Studio T-SQL commands to restore full and differential backups:
RESTORE DATABASE myDatabase FROM DISK = 'd:backupsmyDatabase.BAK' WITH NORECOVERY
GORESTORE DATABASE myDatabase FROM DISK = 'd:backupsmyDatabase_Diff.BAK' WITH RECOVERY
GO
I was having trouble finding out how to view the progress of the restore. I was using Google to search for sql restore status but not finding anything useful. Since I had just paid for a year on Experts Exchange I went there and searched with the same terms, and wouldn’t you know it, the first result was someone answering the same question with a detailed T-SQL command to do what I needed:
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')
Wauw, great query! Work like a charm. Thanks, Roy.