I want to generate a report of the log restores of a database. The GUI has an option to generate the backup history but not the restore history. I am trying to troubleshoot something and this is needed. I can see all the logs in the GUI in the Activity History pane, but don't want to copy/paste. Any possibilities? Through GUI or sql query? Of course, I am talking about log backups taken using RedGate.

If you don't mind writing your own reports, you can access the restore details using SQL commands from within Management Studio.

The SQL Backup restore history details are stored in a SQL Server Compact Edition database on the server. You could use the sqbdata proxy function to access the details. The tables that store the restore history details are:

I prefer writing my own reports - thanks for the tip. This gives me all the information I need. I need a clarification though. The following is the output from a log file. As you can see, the restore started at 9:14:18pm and finished at 9:36:59pm - that is in 22 min 36 sec. What does 313.414 seconds (in the RESTORE LOG line) signify here then?

The 313.414 seconds is the time taken to read and restore the backup data. The rest of the time would be used to re-apply the uncommitted transactions from the redo log prior to the restore process, and also to undo any open transactions from the current restore process, and write them to the redo log.

If you want to confirm that this is indeed the case, try restoring a couple of trx logs with the NORECOVERY option. The total duration taken should be close to what SQL Server reports on the 'RESTORE LOG successfully processed...' line.

Thanks Peter. Restoring with norecovery has indeed brought the restore times down, but not to the level I would have expected. Look at this log for example. Restore log line tells that it took 0.196 seconds, yet the total duration is 20 seconds. What could be the reason?

Thanks Peter for sticking with me. So, I set the trace flags on and reran the restore. Here is the typical log file. Also see the attached image from sql server error logs for the activity while this particular trn log was being restored.

The Restore-Redo process might just be taking a reasonable duration, or it might actually be slow, depending on your setup. If you think it should complete faster, one option may be to look at the virtual log files (VLFs) of your database (see http://adventuresinsql.com/?p=12).

Thanks Peter. I am going to change my process to restore with norecovery by default, and then recover to standby mode only in the end. That should bring down the total restore time. Also, I looked at the VLFs and resized the log file. Will watch over next few days to see the results.