August 22, 2012 at 2:18 pm
Hi,
Easily confused here, I am trying to determine if using BCP to import a large amount of data into a table breaks the log chain?
i.e. SQL Server 2008 R2 Enterprise SP1
Full backup at midnight
Transaction Log Backups every hour on the :15.
Non-Replicated Target Table
Non-Empty Target Table
If we do a BCP in at 3am could we still recover the database to the 5:15am T-Log backup?
I say in this scenario the BCP will be fully logged but after reading the MSDN article my colleague isn't sure.
We've both read the MSDN articles and still don't agree.
Thanks for any insight.
August 22, 2012 at 2:34 pm
First, I have no idea what article you are refering to here. Second, there are no non-logged operations in SQL Server. The BCP is logged, so if you import the data at 3:00 AM and a disk failure requires a restore at 5:15, the data BCP'ed at 3:00 will be there.
August 22, 2012 at 2:39 pm
Cory Blythe (8/22/2012)
Hi,Easily confused here, I am trying to determine if using BCP to import a large amount of data into a table breaks the log chain?
To second Lynn above, the answer to this question is 'No'. You just have a huge log.
If we do a BCP in at 3am could we still recover the database to the 5:15am T-Log backup?
As long as you're in bulk or full recovery modes, yes.
There are no non-logged operations in SQL Server. There are 'minimally logged' operations, but nothing avoids the logs completely. There are exceptions, I'm sure, but you'd have to look long and hard to find 'em.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 2:42 pm
Thank you for the reply that answers my question.
The articles in question were:
http://msdn.microsoft.com/en-us/library/ms162802.aspx
http://msdn.microsoft.com/en-us/library/ms188365.aspx
and
http://msdn.microsoft.com/en-us/library/ms190422.aspx
It was the minimal logging that caused the confusion.
Thanks again.
August 22, 2012 at 2:47 pm
Minimal logging and bulk-logged recovery model: http://www.sqlservercentral.com/articles/Recovery+models/89664/
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
August 22, 2012 at 2:55 pm
GilaMonster (8/22/2012)
Minimal logging and bulk-logged recovery model: http://www.sqlservercentral.com/articles/Recovery+models/89664/
ERG, BAD CRAIG! Giving out bad information again. :blush: My brain's jumping important pieces of information, sorry bout that.
Alright, Gail's quite right, thanks for reminding me. Because you're doing your T-Log backups every 15 minutes the restore to a point in time 2 hours later isn't a problem, as you'd be in a different Log Backup that didn't have THAT bulk-entry. However, if it had ANOTHER bulk entry (and you were on Bulk recovery) restoring that T-Log would be all or nothing. You couldn't point in time it, you'd have to restore it in its entirety.
Now, as long as you're doing backups every 15 minutes, that just means the most you could lose is 14 minutes and 59 seconds of work even in the worst case scenario. The question is how important are those 14 minutes. If they're important, stay in full mode if you use BCP or other minimally logged activities.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 2:58 pm
Evil Kraig F (8/22/2012)
GilaMonster (8/22/2012)
Minimal logging and bulk-logged recovery model: http://www.sqlservercentral.com/articles/Recovery+models/89664/ERG, BAD CRAIG! Giving out bad information again. :blush:
Err, you did? Where?
Now, as long as you're doing backups every 15 minutes, that just means the most you could lose is 14 minutes and 59 seconds of work even in the worst case scenario. The question is how important are those 14 minutes. If they're important, stay in full mode if you use BCP or other minimally logged activities.
59 minutes 59 seconds. Every hour on the 15 minutes.
Restoring to 5:15 will always work (well, unless someone's switched to simple recovery somewhere in the middle), but if you're in bulk-logged recovery you may not be able to do a point-in-time to the middle of a log backup interval.
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
August 22, 2012 at 3:02 pm
GilaMonster (8/22/2012)
Evil Kraig F (8/22/2012)
GilaMonster (8/22/2012)
Minimal logging and bulk-logged recovery model: http://www.sqlservercentral.com/articles/Recovery+models/89664/ERG, BAD CRAIG! Giving out bad information again. :blush:
Err, you did? Where?
Okay, overreaction... Incomplete information. :hehe:
59 minutes 59 seconds. Every hour on the 15 minutes.
Helps if I learn to read...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply