Confused about backups and recovery models

Question

Post reply

Confused about backups and recovery models

DataAnalyst011

SSCarpal Tunnel

Points: 4035
More actions
August 17, 2016 at 10:26 am

#310614

I've been reading several articles trying to wrap my mind around things like...
What does setting a recovery model actually DO? Does it just affect the transaction logs (truncated in simple, retained until backed up in full, etc.)?
Are transaction logs included in a backup? (.bak file)
I know this is DBA 101, and I'm trying to self-server by reading posted articles, but I'm having trouble wrapping my mind around it. Any lifeline would be appreciated!
August 26, 2016 at 11:46 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

andrew gothard SSChampion Points: 12314 More actions · Answer 1

Very briefly:

Full -

allows you to restore to a point in time - say 4 seconds past 5 this afternoon when someone accidentally truncated the X table in live instead of Dev.

if you're taking your t-log backups remotely (remotely can mean to dedicated backup storage or a remote datacentre) you can define a Recovery Point Objective. Ie, this is the Max amount of minutes of data I can lose if I lose a box / database. If you take them every 5 minutes, in principle you're talking about a max loss of 5 minutes worth of data

This, you'd generally want for OLTP

Simple

no point in time restore - only from the last full backup. All changes since then are lost.

Common use-case would be reporting databases after your nightly ETL process

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Here's a good article by Gail Shaw that discusses Recovery Model internals and has some really good links about what's going on under the hood (if that's what you're looking for).

Recovery Models[/url]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 3

Thank you both for the replies. I will check out that link.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

andrew gothard (8/17/2016)
Very briefly:
Full -
allows you to restore to a point in time - say 4 seconds past 5 this afternoon when someone accidentally truncated the X table in live instead of Dev.
if you're taking your t-log backups remotely (remotely can mean to dedicated backup storage or a remote datacentre) you can define a Recovery Point Objective. Ie, this is the Max amount of minutes of data I can lose if I lose a box / database. If you take them every 5 minutes, in principle you're talking about a max loss of 5 minutes worth of data
This, you'd generally want for OLTP
Simple
no point in time restore - only from the last full or differential backup. All changes since then are lost.
Common use-case would be reporting databases after your nightly ETL process

I just added something you missed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 5

You can also take the help of this link to know more about recovery models: http://sqltechtips.blogspot.com/2015/11/-recovery-models.html

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

That blog post is wrong in a few places.

This, for example, is wrong.

Bulk logged recovery model performs much better than simple logged recovery model because it uses minimal log space and minimal logging is required.

So is this

Main draw back of the full recovery model is that, you have to setup a regular transaction log backup to avoid the growth of transaction log files, otherwise the log files keep growing until the next full backup.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass