I have recently installed some maintenace plans to perform database backups for an application database. The database is in full recovery mode so I have setup a full backup to be run once per day and then transaction log backups to run every 2 hours throughout the day. My question pertains to the coordination within SQL Server with respect to the database backup and the transaction log backups.
Let's say the full database backup is in progress and at the same time a trasnaction log backup is fired. What happens? Does SQL Server just skip the tran log backup since it won't be needed? or does it do the backup of the tran log anyway (in case the backup fails).
Another question would be when is it safe to delete the transaction log backup target file? Can it be assumed that after a SUCCESSFUL full backup that the transaction log backup can be deleted?
Since I have seperate maintenance plans in place here I am really trying to find out how/if these tasks are integrated within SQL Server. I'm more familiar with Oracle and the use of RMAN. Specifically for cleanups, RMAN knows what is safe to delete and what isn't and so it will keep a backup on disk if it feels it is needed. Is there similar functionality in SQL Server?
Hello,
You're correct: a full backup will block a tran log backup (until the blocking backup cmd completes), and vice-a-versa.
Following a successful full backup, you can safely delete all tran log backups taken prior to the full backup.
There is no equivelant RMAN functionality provided by SQL Server. There is, however the EXPRIEDATE and RETAINDAYS clause you can make use of in the backup command. Check out BOL for more info, but it may help you...
Cheers,
Rob
|||Actually, that was the case in SQL 2000.
In SQL 2005, you can concurrently run database backup and tran-log backups. The tran-log backup would simply contain the range of LSNs that span the time of the full backup. It would be used as the starting point for rolling forward from restoring that full backup.
No comments:
Post a Comment