Restore Timing Transaction Logs versus Differentials

Currently we backup out SQL Server 2005 databases with a combination of full, differential and transaction log backups and plan on continuing this practice. Currently all backups are written to physical servers other than the servers where the DBs are running and all backups are also written to tape. Our tape backups are costly and I am considering no longer writing the differential backups to tape to reduce costs. Before making this decision I would like to understand how much longer it would take to recover several days of transaction log backups, versus a single differential and only a few hours of transation log backups. Does anyone have any any information about the time difference? Any ratios or rules of thumb?

Thanks,
Julia

Stopping the tape copies of differentials seems like a reasonable plan IF you kept two or three of the DIFFERENTIAL BACKUP copies on disk. (I prefer to keep all differentials since the last FULL BACKUP, tossing them only after the next FULL BACKUP is verified.

Time to recover: Seems like the major time issue is the manual process of handling each individual restore. The fewer files to restore, the less 'manual' time. The greated the number of files involved, the greater the risk of a corrupt or damaged file.

|||
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||

There are so many variables on that, I wouldn't hazard a guess.

You could run a comparision test on another server, comparing the timings.

|||Has anyone run these types of timed tests comparing the restore times from 2 types of backups?|||

That is purely depends upon the number of transactions on your database to restore, see this blog http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx on the performance stats for backup & restore tasks.

EgleK wrote:

Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?