Transaction Log v. Differential backup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transaction Log v. Differential backup

Hi all, I hope someone can shed some light on this subject for me! I’m trying to find a definitive answer to what would be the risks involved in using the TLog in connection with a Full backup for the following Disaster Recovery Strategy. The method I am proposing for a disaster recovery scenario is that a full back up is taken on a Sunday night and sent over our network to our DR site and restored into a backup server. Transaction Logs would be backed up and sent across the network during the course of the next day from 07:00hrs to 20:00hrs, say at 15 minute intervals, and then restored into the backup server. This would also happen on a Tuesday, Wednesday etc till the following Saturday night and then the whole process could start again. Once these TLogs are sent to the DR site they are restored. The syntax that is in use to currently backup the Log is as follows: BACKUP LOG [GDC]
TO [GDC_LOG_BACK]
WITH NOUNLOAD, NOSKIP, INIT Can you use the VERIFY statement with the Backup of the TLog to ensure the TLog is usable for restoring into the backup database?
Assuming the VERFIY method can be used with the TLog, will the TLogs ever run the risk of failing at the point of restore into the Backup server? If the Production database fails between the backup of a TLog, what can be done to retrieve as much data as possible to the point of failure, assuming all previous TLogs have been applied in the correct order? Any ideas or improvements on this would be a great help! Thanks in advance DJ (moved from Logshipping forum)
Differential backups are designed to compensate for the large amount of time required to perform a full backup. They utilize a similar mechanism to store a copy of only the data that has changed since the last full backup. In these cases, differential backups will consume significantly fewer resources than a full backup and may even be able to process without significantly impacting database performance. The exact mix of full and differential backups depends upon a variety of factors unique to your organization. Be sure to consider the length of time required to perform the backups and the impact that might have on your database performance. You can use RESTORE VERIFYONLY that verifies the backup but does not restore the specified backup on the server. For further information on Disaster Recovery plan refer tohttp://www.sql-server-performance.com/greg_robidoux_interview.asp link. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks Satya for your reply, The reason i am proposing the solution using the TLogs is because the current strategy uses TLogs and restores them straight away onto the DR Server so that at any point of needing to invoke the disaster recovery our data will be up to date and we can have a minimal downtime for the business users. I know that the differential is preferential as the time between full backups is typically a week but if the TLogs are being restored and backed up and restored to the DR site straight away whats the need for doing a differential?
The other idea that one of the guys here suggested is that a full backup is done on sunday, TLogs done during a monday, a differential is done on monday night.
The full backup from sunday is restored to the DR site monday night,
The Differential from monday is applied in the DR site.
TLogs are done tuesday, a differential is done tuesday night. the full backup from sunday night is applied to the DR site Tuesday night, the differential from tuesday night is then applied. This would happen throughout the week. Surely there are downsides to this as the differential that will be taken each night will grow? This is partly why i went with log shipping over the network even if the frequency has increased the risk of network failure from sendin large files de-creases?
quote:The syntax that is in use to currently backup the Log is as follows: BACKUP LOG [GDC]
TO [GDC_LOG_BACK]
WITH NOUNLOAD, NOSKIP, INIT Can you use the VERIFY statement with the Backup of the TLog to ensure the TLog is usable for restoring into the backup database?
Assuming the VERFIY method can be used with the TLog, will the TLogs ever run the risk of failing at the point of restore into the Backup server? If the Production database fails between the backup of a TLog, what can be done to retrieve as much data as possible to the point of failure, assuming all previous TLogs have been applied in the correct order?

Cheers again for the help DJ
Why not utilise Log shipping in this scenario as once the full backup is restored to the DR server, the logs will be ported automatically with an easy way. Refer to the books online and http://www.sql-server-performance.com/sql_server_log_shipping.asp link for further information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>