SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with SQL Server Recovery Models


Working with SQL Server Recovery Models

Author
Message
prettsons
prettsons
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4051 Visits: 1467
Comments posted to this topic are about the item Working with SQL Server Recovery Models

SQL Database Recovery Expert :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)

Group: General Forum Members
Points: 458747 Visits: 43752
Good overview for beginners. There are a couple of points that I think should have been included or are actually incorrect.

First, in the Simple Recovery mode section, you say...

This is done to reclaim the log space, and thus keeping the log space consumption to minimum.


It may be a matter of semantics and it may be the goal of the DBA but that's not why SQL Server truncates the log. The system still writes to the log so it can roll back a transaction that goes awry but, once a transaction succeeds or fails and because log backups are not an option in the Simple Recovery mode, there's no longer a need to keep the information in the log, so it gets rid of it.

You also didn't mention that the Simple Recovery mode will also do minimal logging.

In the Bulk Logged section, you say...

You still have an option to restore your database to a point in time until a bulk operation is recorded in the log.



I realize this article wasn't about backups but because backups are so closely tied to the Recovery mode and because newer folks (and even some seasoned DBAs) don't understand what the Bulk Logged Recovery mode actually does, I think some additional information should have been included in the article. The statement above is also a bit confusing.

First, and to be clear, you can, in fact, resume PIT (point-in-time) restores after switching back to Full Recovery. But, you need to be keenly aware those files that were in the Bulk Logged Recovery mode, even for a microsecod, can only be restored in an all or nothing manner even if it was for only a microsecond. Of course, if the PIT you're looking for is during such a file as is an error, you can't restore that Bulk Logged file and you could be missing a whole lot more data than you planned on. And there's no such thing as being able to skip a single log file. Only unbroken log chains can be restored. To that point, it would also have been worth the mention that doing a Differential or Full Backup immediately after returning to Full Recovery is absolutely the right thing to do so that you can actually do a PIT restore within the timeframe that the database was in the Bulk Logged recovery mode.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TravisDBA
TravisDBA
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5660 Visits: 3069
Not sure why an article had to be published on a basic topic like Recovery Models that can be easily gleaned from Books Online. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)

Group: General Forum Members
Points: 458747 Visits: 43752
TravisDBA (3/5/2013)
Not sure why an article had to be published on a basic topic like Recovery Models that can be easily gleaned from Books Online. :-D


Because there's a lot of people that don't know where the {f1} key is. :-D It's also a chance for a new author to get his feet wet.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TravisDBA
TravisDBA
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5660 Visits: 3069
Jeff Moden (3/5/2013)
TravisDBA (3/5/2013)
Not sure why an article had to be published on a basic topic like Recovery Models that can be easily gleaned from Books Online. :-D


Because there's a lot of people that don't know where the {f1} key is. :-D


Man! that's rich!:-P I haven't heard that lame of an excuse in a long time. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)SSC Guru (458K reputation)

Group: General Forum Members
Points: 458747 Visits: 43752
BWAAA-HAAAA!!!! I "hear" it every day. Just look at 50% of the posts on these and other forums. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search