Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Point-in-time restore Expand / Collapse
Author
Message
Posted Thursday, February 17, 2011 1:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Koen Verbeeck (2/16/2011)
Shawn Melton (2/16/2011)
Duncan Pryde (2/16/2011)
[quote]Although I haven't done that exam myself as I upgraded directly from 2005 to 2008 MCITP, most of the Admin exams I've done have contained a fair amount of backup and recovery questions, so it does pay to know your stuff.

Duncan

This would be NDA violation sharing this information would it not?...just FYI.


Hmmm, you could derive that information also from looking at the "Skills Measured" section at the Microsoft Certification Website, as it contains relative percentages.


That's what I thought too. If I'd mentioned specifics about individual questions that would not be good, but it was just a general observation.

Duncan
Post #1065471
Posted Thursday, February 17, 2011 1:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Koen Verbeeck (2/16/2011)
Shawn Melton (2/16/2011)
I call shenanigans. How can the answer not be no? If a database is set to Bulk-Logged recovery model that means some transactions are minimally logged. That would mean the complete transaction would not exist in the database log file, how can you restore to that when SQL cannot rebuild the whole transaction?


The correct answer stated that you could restore to a point in time if there were no bulk-logged changes.
BOL however contradicts itself. In one page they say it isn't possible, on another they say it can be done if no bulk logged operations have occurred.

Even the references that you gave contradict themselves.


Essentially the Bulk Logged recovery model is the same as the Full recovery model except that some operations are minimally logged. As long as none of those operations happen, you effectively have a database in the Full recovery model.

I did in fact perform some fairly extensive tests before posting this question, using SELECT INTO as my minimally logged operation. As long as it wasn't in the relevant backup I could restore to a point in time. I'm pasting my code below for anyone who wants to try for themselves.

This first bit of code creates the database, a table and inserts some records. It does a bulk operation, performs a log backup, adds some more records then drops the table.

-- Create our database
CREATE DATABASE DisasterRecoveryDemo;
GO

-- Make sure we're in bulk-logged recovery mode
ALTER DATABASE DisasterRecoveryDemo
SET RECOVERY BULK_LOGGED;
GO

USE DisasterRecoveryDemo;
GO

-- Create a table for our test data
CREATE TABLE ImportantData
(
ID int,
EntryDateTime datetime
);
GO

-- Insert some initial values
INSERT INTO ImportantData (ID, EntryDateTime)
VALUES(1, GETDATE()), (2,GETDATE()), (3, GETDATE());
GO

-- Perform a full database backup
BACKUP DATABASE DisasterRecoveryDemo TO DISK = 'DisasterRecoveryDemo_Full.bak';
GO

-- Perform bulk-logged operation
SELECT ID, EntryDateTime
INTO ImportantData2
FROM ImportantData;
GO

-- Perform a log backup. Now the just-inserted record will be backed up
BACKUP LOG DisasterRecoveryDemo TO DISK = 'DisasterRecoveryDemo_Log.bak';
GO

INSERT INTO ImportantData2 (ID, EntryDateTime)
VALUES(4, GETDATE()), (5,GETDATE()), (6, GETDATE());
GO

-- Make a mental note of the contents of the table
SELECT * FROM ImportantData2;
GO

-- Check the current time
SELECT GETDATE();
GO

-- If you wait for a bit you can do a simulated disaster, e.g.
WAITFOR DELAY '00:00:10';
GO

DROP TABLE ImportantData2;
GO

Making a note of the time returned by the GETDATE statement, you can run the following code to get the database back to just before you dropped the table

USE master
GO

-- Set the database to single user so that no other connections are allowed
ALTER DATABASE DisasterRecoveryDemo
SET SINGLE_USER;
GO

-- Back up the remainder of the log using NORECOVERY mode - called a tail log backup
BACKUP LOG DisasterRecoveryDemo TO DISK = 'DisasterRecoveryDemo_TailLog.bak'
WITH NORECOVERY;
GO

-- Begin the restore process with restoring the full backup
RESTORE DATABASE DisasterRecoveryDemo FROM DISK = 'DisasterRecoveryDemo_Full.bak'
WITH NORECOVERY;
GO

-- Next restore the first log backup
RESTORE LOG DisasterRecoveryDemo FROM DISK = 'DisasterRecoveryDemo_Log.bak'
WITH NORECOVERY;
GO

-- Finally restore the tail log backup, stopping at just before the disaster
RESTORE LOG DisasterRecoveryDemo FROM DISK = 'DisasterRecoveryDemo_TailLog.bak'
WITH RECOVERY, STOPAT = '<time noted earlier>';
GO

-- Get the database back into multi user mode
ALTER DATABASE DisasterRecoveryDemo
SET MULTI_USER;
GO

USE DisasterRecoveryDemo;
GO

-- Check the data is restored
SELECT * FROM ImportantData2;

And to clean up

USE master
GO
ALTER DATABASE DisasterRecoveryDemo
SET SINGLE_USER;
GO
DROP DATABASE DisasterRecoveryDemo;
GO

By moving the position of the SELECT INTO statement, you can see that point in time recovery is not possible if it occurs in the backup you use to restore to a point in time and you get this error:

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.

Hope that helps,

Duncan
Post #1065484
Posted Thursday, February 17, 2011 1:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
Great, thanks for the demo code!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1065485
Posted Thursday, February 17, 2011 1:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Koen Verbeeck (2/17/2011)
Great, thanks for the demo code!


No problem. I originally wrote it to show how if a database was using Full recovery, you could always recover to a point in time by taking a tail log backup as long as you had taken a full backup at some point. It followed a minor incident where someone deleted some data and then in the ensuing panic someone else restored the most recent full backup over the top, resulting in unrecoverable data loss! (Fortunately it wasn't terribly important data).

I just adapted the script in order to test the scenario before writing the question.

Duncan
Post #1065488
Posted Thursday, February 17, 2011 5:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:16 AM
Points: 935, Visits: 2,687
Duncan Pryde (2/17/2011)
[quote]Essentially the Bulk Logged recovery model is the same as the Full recovery model except that some operations are minimally logged. As long as none of those operations happen, you effectively have a database in the Full recovery model.


Thanks for the code.

For all purposes then you should not rely on bulk-logged recovery model if you want point-in-time restore for any transaction. Even though you can in some situations do it, you still can't do it at anytime. If you want to guarantee or greatly depend on being able to do a point-in-time recovery your best option would be to choose Full recovery model. If you don't mind loosing some data then bulk-logged could be used.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1065588
Posted Thursday, February 17, 2011 6:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Shawn Melton (2/17/2011)
For all purposes then you should not rely on bulk-logged recovery model if you want point-in-time restore for any transaction. Even though you can in some situations do it, you still can't do it at anytime. If you want to guarantee or greatly depend on being able to do a point-in-time recovery your best option would be to choose Full recovery model. If you don't mind loosing some data then bulk-logged could be used.


That's correct, yes. As I said in the question explanation, the recommended approach is to switch to bulk-logged before any bulk operations and switch back to full afterwards. The second link (http://msdn.microsoft.com/en-us/library/ms190203.aspx) explains it quite well.

Duncan
Post #1065600
Posted Thursday, February 17, 2011 7:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:16 AM
Points: 935, Visits: 2,687
Duncan Pryde (2/17/2011)
That's correct, yes. As I said in the question explanation, the recommended approach is to switch to bulk-logged before any bulk operations and switch back to full afterwards. The second link (http://msdn.microsoft.com/en-us/library/ms190203.aspx) explains it quite well.

Duncan

Ah must have not paid enough attention to the explanation

Thanks for correction my knowledge bank on this one.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1065649
Posted Thursday, February 17, 2011 7:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Shawn Melton (2/16/2011)
This would be NDA violation sharing this information would it not?...just FYI.


OMG!! Run!! everbody scatter!! it's the exam po po... the Test POLICE are on to us... They are among us! They are among us!

If only there was a place where we could go and exchange information and ideas without having to worry about these things...
Post #1065669
Posted Monday, February 21, 2011 11:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
SanDroid (2/17/2011)
Shawn Melton (2/16/2011)
This would be NDA violation sharing this information would it not?...just FYI.


OMG!! Run!! everbody scatter!! it's the exam po po... the Test POLICE are on to us... They are among us! They are among us!

If only there was a place where we could go and exchange information and ideas without having to worry about these things...


He's even wearing his po po glasses...

Great question. I had to think about it for a minute and guessed at the right answer thinking that the full and trans log backups BEFORE the bulk activity would still be valid, so why COULDN'T I do a point in time recovery prior to that bulk activity? Just made sense logically. I was unsure about AFTER, though, and assumed that since my data in the bulk activity would NOT be logged (just the bulk activity itself), then I could NOT get point in time recovery after.

Maybe my logic is wrong but it matched the answer :) I hope it matched because I understand the model...


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #1067215
Posted Thursday, February 24, 2011 1:17 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 26, 2011 4:13 PM
Points: 689, Visits: 410
Hi Folks,

I've been thinking about this question, and looked through a fair bunch of Paul Randal's stuff around this issue and figure that the correct answer is actually missing: For me the correct answer is:
Maybe. Mabye you can recover to point-in-time, if there are no minimally logged entries in the log, or you are covered by a differential backup or whatever, but don't be an idiot and count on it.

Smart practice is not to see what you can get away with, but to make sure you do everything that you can not get away without.

Paul, in responding to the thought that now you have two full backups you can delete the oldest one, said something like: How do you know the current backup isn't corrupt? If it is corrupt then you need that backup you are about to delete!

Regards
David

Post #1069268
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse