Restart Log Backups

Question

Restart Log Backups

Viewing 15 posts - 16 through 30 (of 45 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

I was tempted with the first answer as it seemed correct. But a little bit of logic indicated me that there's no reason for the database backups to be incomplete so that it would be necessary to do a full backup. For the contrary, the log wouldn't be reliable after changing the recovery model from full to simple and back to full.

Nice question, I'm glad to have read all the answers before making a choice.

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

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 2

I actually just dealt with this in a slightly different way. We're doing a purge on a large DB and the app owner wanted a quick way to roll back and re-establish the log chain if anything went wrong. The decision was to take a snapshot, restore from that if there was an issue, and then do a diff. I actually wrote an article about it but haven't submitted it yet because it's still being reviewed by someone else before I send it in.

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 3

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

June 28, 2012 at 9:34 am

#1506697

Thanks Steve.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Good question Steve.

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

Lon-860191 SSCrazy Points: 2449 More actions · Answer 5

Lon-860191

SSCrazy

Points: 2449

June 28, 2012 at 9:50 am

#1506711

Definitely a gotcha!

Drenlin Ten Centuries Points: 1099 More actions · Answer 6

I have been looking at log backups this week so I was glad for this question to confirm what I have been reading and my understanding.

However, there was no mentioning of the previous full backup in the question. Am I guessing that a similar error message would occur if attempting to perform a log backup if there was no full backup? A previous post only mentioned attempting a differential if there was no full backup.

iBar SSCertifiable Points: 5872 More actions · Answer 7

iBar

SSCertifiable

Points: 5872

June 28, 2012 at 10:18 am

#1506732

Nice one. Thanks Steve.

Ken SSCoach Points: 17191 More actions · Answer 8

Ken

SSCoach

Points: 17191

June 28, 2012 at 10:25 am

#1506737

Learned something new, thanks Steve

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 9

Drenlin (6/28/2012)
I have been looking at log backups this week so I was glad for this question to confirm what I have been reading and my understanding.
However, there was no mentioning of the previous full backup in the question. Am I guessing that a similar error message would occur if attempting to perform a log backup if there was no full backup? A previous post only mentioned attempting a differential if there was no full backup.

If there's no full you won't be able to take a differential backup so that doesn't have to exist. I suppose it could be argued that since there was no statement that there was a full backup that a full may be required. However, in most people's environments it's a safe assumption (at least when it comes to answering a QotD) that a full backup exists.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

Glad you liked it, but I can't take credit for discovering this. Mr Randal, of SQL Skills, taught me this. Now I really want to get to one of his Immersion classes.

Doug 53730 SSC-Addicted Points: 480 More actions · Answer 11

Great question. If I wanted to start performing tlog backups on a database yesterday I would automatically run a full backup after setting to full recovery model. Today I know better.

davoscollective SSCertifiable Points: 6355 More actions · Answer 12

Normally I answer these without looking up BOL first but this time I thought I'd double check.

Unfortunately this myth comes straight from the horse's mouth:

"Before you can create the first log backup, you must create a full backup. "

BOL: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

davoscollective (6/28/2012)
Normally I answer these without looking up BOL first but this time I thought I'd double check.
Unfortunately this myth comes straight from the horse's mouth:
"Before you can create the first log backup, you must create a full backup. "
BOL: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Took a bit to find this reference, but if I read it correctly they may be talking about when a database is first created. Based on the question, we could make a practical assumption that there was a full backup taken earlier.

I created a new database and both a t-log backup and a differential backup failed. I needed to take a full backup before either of those worked.

Restoring a full backup, i could take either after words.

davoscollective SSCertifiable Points: 6355 More actions · Answer 14

Thanks for the clarification Lynn, it makes more sense in that context.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 15

Interesting question! Thank you!

But I was confused with the alternative:

"Take either a full OR differential backup and then a log backup"

Shouldn't this be:

"Take either a full AND differential backup and then a log backup"?

One question though: even if one could back up the log after only a differential backup, wouldn't that be useless without the full backup (the diff will not restore)?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA