February 21, 2012 at 11:36 am
Nice and easy. As someone said before, it's good to go back to the basics.
In your experience, how often have you seen DBs with Bulk-Logged recovery models and what are they used for?
Personally I have seen none, most are either simple or full.
Thank you,
"El" Jerry.
February 21, 2012 at 11:49 am
EL Jerry (2/21/2012)
Nice and easy. As someone said before, it's good to go back to the basics.In your experience, how often have you seen DBs with Bulk-Logged recovery models and what are they used for?
Personally I have seen none, most are either simple or full.
Thank you,
"El" Jerry.
Bulk-logged can be useful if you have a specific period of time in which a lot of bulk operations take place (e.g. a daily load of imported data or so). You can mitigate the risk of not having point-in-time restores by taking a log backup just before and just after the bulk operations take place; now the time window for the bulk operations is the only time window where you can't restore to a point in time. And you can gain performance, because less data has to be written to the transaction log before the transaction can commit.
A similar benefit can be achieved by switching the recovery model to simple before the bulk operations, but then you have to take a full backup after the operations complete and you switch back to full recovery, otherwise you run the risk of severe data loss.
February 21, 2012 at 12:46 pm
GilaMonster (2/21/2012)
SqlMel (2/21/2012)
Easy question. Thanks!It's now down to 2% 🙂
Nils Gustav Stråbø (2/21/2012)
Good and easy question. Thanks.I'm surprised that 21% believes that you can do a point in time restore of a database in simple recovery.
19% answered 'all of the above' and 2% answered only in simple recovery model.
That 19% is the part that alarmed me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 21, 2012 at 12:46 pm
EL Jerry (2/21/2012)
In your experience, how often have you seen DBs with Bulk-Logged recovery models and what are they used for?
A couple, but I suspect that was due to lack of knowledge.
Bulk-logged isn't a recovery model that a DB should be in permanently. If point-in-time recovery is important, the DB should be mostly in full recovery, switched to bulk-logged only for particular operations and for as short a time as possible before being switched back to full recovery (and a log backup taken)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 3:22 pm
I thought the question was poorly phrased. I guessed that it was essentially "Full Model requires log backups to guarantee a point in time restore to any moment throughout the time of the log backups." Bulk Logged model with log backups cannot guarantee you can restore to any point in time.
GilaMonster: I thought you could do a point in time restore up to the first bulk-logged operation in a log backup, then had to skip to the end. So in your example, 09h45 to 10h00 is the only "dark zone." Am I mistaken?
It's also worth noting that when you change the Recovery Model from Simple to Full or Bulk Logged, you have to do a full backup before the database will actually change to the new model. Switching to the Simple Model always breaks the backup chain at the last log backup.
David Lathrop
DBA
WA Dept of Health
February 21, 2012 at 5:13 pm
GilaMonster (2/21/2012)
You can determine it before you try. Both the backup header (RESTORE HEADERONLY) and the msdb backup tables have a column called something like HasBulkLoggedData. If that is 1 for a log backup, it's a log backup under bulk-logged recovery and contains some minimally logged operations.
As for trying to restore to point-in-time within that log backup
Msg 4341, Level 16, State 1, Line 2
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
I learned something new with this explanation... Thanks a lot Gail...
February 21, 2012 at 6:04 pm
SQLRNNR (2/21/2012)
GilaMonster (2/21/2012)
SqlMel (2/21/2012)
Easy question. Thanks!It's now down to 2% 🙂
Nils Gustav Stråbø (2/21/2012)
Good and easy question. Thanks.I'm surprised that 21% believes that you can do a point in time restore of a database in simple recovery.
19% answered 'all of the above' and 2% answered only in simple recovery model.
That 19% is the part that alarmed me.
Dont be alarmed. It is not what you think. It all depends on the interpretation on the answers (and your english or knowledge somewhere :hehe: ).
In my case, I got it wrong because I am doing only simple model backup and I can go back to any point in time (that the backup has been done, surely, but still, I can go back!). So, for me, because the 'Simple' answer was good (is not, but I did not know at the time... still not sure to understand why, so I have to work on that...) and I knew the "Full model" answer was good, the only logic choice was "All of the above", even if I had no idea what is a Bulk logged model (still don't... did I tell you that I have to learn on that? 🙂 )
So, on the 688 answers (at the writing time), I am pretty sure I am not the only person who had interpretation problem or is still learning, so don't be alarmed, but happy that the question might have pop-up other questions for the people that got it wrong
Thanks for the question Stuart. Still learning which is really, really good!
February 21, 2012 at 10:34 pm
EL Jerry (2/21/2012)
Nice and easy. As someone said before, it's good to go back to the basics.In your experience, how often have you seen DBs with Bulk-Logged recovery models and what are they used for?
Personally I have seen none, most are either simple or full.
Thank you,
"El" Jerry.
Hi El Jerry,
We don't have any databases in Bulk recovery model, however we change the databae to bulk recovery model for some dbs when we do reindexing. This option of bulk recovery is really helfull for those operation.
--- Babu.
February 22, 2012 at 1:13 am
tilew-948340 (2/21/2012)
It all depends on the interpretation on the answers (and your english or knowledge somewhere :hehe: ).In my case, I got it wrong because I am doing only simple model backup and I can go back to any point in time (that the backup has been done, surely, but still, I can go back!). So, for me, because the 'Simple' answer was good (is not, but I did not know at the time... still not sure to understand why, so I have to work on that...) and I knew the "Full model" answer was good, the only logic choice was "All of the above", even if I had no idea what is a Bulk logged model (still don't... did I tell you that I have to learn on that? 🙂 )
I disagree that this has anything to do with interpretation of the question or mastery of English; it is simply a lack of SQL Server backup/restore knowledge. (And I don't mean this in a negative way; noone can know everything about SQL Server and you indicate yourself that you still have to learn in this area).
The term "point-in-time restore" (or, more commonly, "point-in-time recovery") refers to the ability to restore the database to a consistent state as of any point in time, which goes way beyond the ability to the ability to restore to only the points in time when a backup was taken. So you can for instance tell SQL Server to restore the database to the state it was in at 3:42 pm; SQL Server will then do that - every transaction that was committed before 3:42 pm will be restored; every transaction that was not yet committed at that time will be gone, just as if you shut down the server at 3:42 pm and then restarted it. This feature can be incredibly useful, for instance when you know that your co-worker executed that dramatic "DELETE FROM Customers" statement without WHERE clause on 3:43, or when you know that the drive holding the data file failed on 3:43 pm. With full backups only, or full + incremental backups, you'd always lose all the changes that were made between the last backup and 3:42; with point-in-time restore, you lose nothing at all (except, of course, if you discovered the disaster too late and work was still done after 3:43 - but that work would probably be suspect anyway).
The full recovery model enables point in time restore. It does this by keeping a detailed log of every change made in a special file, the transaction log (the .ldf file). When you do a point-in-time restore, you first restore the last full or incremental backup, then SQL Server can use the tranaction log to redo all changes that were made since (and undo changes that were made as part of transaction that were not yet committed at the designated point in time). Enabling full recovery also requires you to schedule regular backups of the transaction log, otherwise this file will happilly keep growing until you run out of disk space and start getting errors. SQL Server will only start reusing log file space if it knows that the data in that part of the log file is no longer needed for restores - which is only the case if it knows that you backed up the data somewhere safe (the last part is an assumption of SQL Server; it does not actually know or check what you do with the backups of the transaction log).
The simple recovery model does not enable point in time restore. It does use the same transaction log file that the full recovery model also uses, but it won't keep the information that is there quite as long. Since the transaction log is also used to recover databases to a consistent state after an unpexpected shutdown + restart of SQL Server, entries in the transaciton log will always be kept as long as necessary for that purpose. But after that, they will be reused. Hence, the simple recovery model does not need (and in fact I think it doesn't even allow) you to do log backups.
Finally, the bulk logged recovery model is basically a special version of the full recovery model. Some changes (the so-called bulk operations, such as BULK INSERT) are logged in a different way - a way that takes far less space (and hence less time), but that also hampers the ability to restore to any point in time. You can still restore to the end of the log file. And if there are no bulk operations in the log backup, you can still restore to any point in time for that log backup. So if you use bulk-logged recovery and schedule hourly log backups, your maximum data loss is now reduced to one hour in the one-hour time frames that include at least one bulk operation, and none in the one-hour time frames that don't include any bulk operations.
Most companies use either simple recovery all the time, or full recovery all the time, or full recovery most of the time, temporarily switching to bulk-logged when a job starts that does a lot of bulk operations (and switching back to full + taking an extra log backup right after that finishes).
February 22, 2012 at 5:24 am
Thanks very much for the explanation Hugo. It certainly help to understand the difference between point-in-time restore and a restore from simple backup
Very, very much appreciated ! thanks again
February 22, 2012 at 8:04 am
Nice straightforward question, and good discussion. Thanks, Stuart.
Tom
February 23, 2012 at 6:25 am
Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
March 7, 2012 at 5:06 pm
GilaMonster (2/21/2012)
EL Jerry (2/21/2012)
In your experience, how often have you seen DBs with Bulk-Logged recovery models and what are they used for?A couple, but I suspect that was due to lack of knowledge.
Bulk-logged isn't a recovery model that a DB should be in permanently. If point-in-time recovery is important, the DB should be mostly in full recovery, switched to bulk-logged only for particular operations and for as short a time as possible before being switched back to full recovery (and a log backup taken)
Previous organization (no names) had a LOB application where the database was in Bulk-Logged for years. When the DBA responsible for the system was riffed, I changed it to full recovery. The very next day by log file was 30+ GB. Turned out the other DBA was rebuilding every index on every table every night. It screwed up a restore to another server that another department used for reporting because that server didn't have enough room to restore the full backup. Cleaned up that mess too.
January 5, 2015 at 11:07 pm
EZ.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
October 11, 2015 at 12:02 pm
Though its a very simple question, backup and recovery topic is so important for anyone working with SQL Servers..
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply