October 7, 2002 at 6:26 am
Hi everybody.
I've seen in these forums you've talked a lot about the Simple Recovery Mode. People are with some problems about the log file size, and then everybody tells them to use the Simple Recovery Mode.
The only thing I can't understand is why not to use the Bulk Logged Recovery Mode. I found at BOL it is as good as the Simple Mode, but it lets you use the log backup and restore the datafile to the point of failure. But till now I'm not sure what're the pros and cons of using each mode. Could anyone who has this experience tell us 'bout that?
Thanks a lot.
Alexandre Aschenbach
Alexandre Aschenbach
October 8, 2002 at 10:28 am
The bulk logged recovery mode does not allow point in time recovery. It allows you to recover to the end of a transaction log backup. Also bulk copy operations are minimally logged.
In my experience you usually need to be able to restore to a point in time (which means using the full recovery model) or you don't need it (which means using the simple recovery model). If I don't plan to ever restore to a point in time then I don't want to worry about transaction log backups nor the associated growth in the transaction log, so I use the simple recovery model.
When I have critical data, I use the full recovery model because I can't afford to lose anything, whether done by a bulk copy operation or not. I must be able to restore to a specific point in time.
With the bulk logged recovery model, I won't be able to restore to a point in time (according to Microsoft BOL), but I'd still have to worry about transaction log file growth.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 5:37 pm
Simple recovery - truncates the logifle when a checkpoint is issued to it. This means that the only reliable backups you can make and use are Full Backups. This also means that the transaction log should not get too far out of hand depending on the type of transaction taking place.
Bulk Logged Recovery - The transaction log is not truncated, but bulk inserts are not logged. So as long as you make no bulk inserts you can make Full, Differential, and TL backups which will restore just fine. But if you do a combination of any and decided a bulk insert needs to be done then you need to perform a Full Backup before you can use the others. This means you have higher recovery possibilities, but also the log will continue to grow until freed.
Full recovery - Same as before except the bulk inserts are logged as well. This makes even higher recoverability available with Full, differential and TL backups available in most all cases. However this also means a lot more data will be logged to the transaction log especially if you do bulk inserts and thus it's growth will be faster and larger until freed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 9, 2002 at 7:12 am
Thanks everybody.
As I could see in here, the only way to have point in time recovery is to use the Full Recovery.
But, one more question, is there a way to have point in time recovery and make Log Backups each hour and truncate the log at that point? This way I could save the Logs in a removable media and save space at the server's hd. When I need the restore I could just restore the backups to the server's hd and that's it.
Is it possible?
Thanks a lot everybody, specialy rmarda and Antares686. Hope in the future I can help you back.
Thanks again.
Alexandre Aschenbach
Alexandre Aschenbach
October 9, 2002 at 12:22 pm
I use the Bulk Logged Recovery and hourly transaction log backups and do a continual restore to another server. This allows at most one hour lag time, which is acceptable in my environment.
It's my understanding that the transaction log backup will truncate the transaction log after it is complete.
October 9, 2002 at 2:29 pm
I do the same as pnewhart except that my trans log backups are 5 minutes apart. For me and our purposes, thats as good as a point in time.
October 9, 2002 at 2:29 pm
I do the same as pnewhart except that my trans log backups are 5 minutes apart. For me and our purposes, thats as good as a point in time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply