SQL Server Performance Forum – Threads Archive

Transaction Log Backup & Restore

Hi, I’ve set up backup jobs to perform a full database backup daily and a transaction log backup hourly during working hour. The transaction Log backup job has been set to append and truncate the transaction log. Because I’ve set the backup to append, I’m having a problem controlling the size of the log backup file as it’s growing every time the log backup job runs. Is there another way of doing this which allows me to control the size of the log backup file as well as having the option to restore to a log backup to a point of failure if the need arises? Many thanks in advance.

Not if you use append. Most people here would agree with me. You really need to have each transaction log backup on a seperate file though. You then don’t have to worry about write contention on the file. You can also be restoring from files while the log backups continue if you have a warm standby server. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Increase the schedule of transaction log backup in order to keep the size min. as compared to the current one. As you’re restoring the Tlog its better to use WITH INIT instead of appending, if you’ve such disk free space issues. 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 for the suggestions. I’ve tried backing up the log WITH INIT which resolved the log backup file size isses, but when I try to restore this log file; it falls over saying that the log in the backup set is too late to apply. My log backup job is based on the following SQL query: BACKUP LOG [DBname] TO DISK = N’C:LogBackupFile est.bak’ WITH INIT , NOUNLOAD , NAME = N’Transaction Log backup’, NOSKIP , STATS = 10, NOFORMAT Can you please tell me where I’m, going wrong. Thanks

It means some changes were occured between the log backups, nothing to worry.
Simply take full backup of database and restore on the standby server, and then re-enable the Tlog backup schedule.
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.

I’m not quite sure what you mean by re-enabling the Tlog backup schedule. I can restore the full backup onto the standby server without a problem; it’s restoring the Transaction log backup I’m having the problem with. I still get the error messages stating that the backup set is too early late to apply. Many Thanks

Meaning that your Transaction log back ups are out of sync with Full database back ups.
What happend was that one of your steps over wrote the previous TL back up that was not applied.
Apply a full database back restore then perform a fresh TL back up and restore.

Also, remove WITH INIT from your BACKUP command as long as you are using backup dump devices – it makes your transaction log backups worthless (you can not do point in time and point of failure restores because you do not have transaction log backup history- each new run of the job overwrites the previous one).
Consider that writing all transaction log backups into single dump device creates additional point of failure (if file is corrupt – _all_ of the transaction log backups are corrupt). Instead consider creating a database maintenance plan to control transaction log backups. simas

If you are using backup devices, you have to append until the next full backup. Lookup BACKUP in Books Online. It’s possible to backup to files instead. In that case, you just backup each log backup to its own file. You then restore the files in order. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Many thanks to you all for you suggestions. I’m now able to restore my full and log backups onto a standby server. I used the Database Maintenance plan to create my log backup jobs which backs up each log backup to a separate file.

]]>