THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Pop Quiz: Restore a Database to the Point in Time when a Full Backup Started?

Recently we've had to interview some SQL Server DBA candidates for our team, and we were looking for the type of open-ended technical questions that would draw out interviewees and let us get a sense of their thought process. A surprisingly simple question came up that seems to work well - it makes the candidate think through and explain some internals of SQL Server in a way that I think reveals whether or not they have a handle on fundamentals about the transaction log and backups. The question is simple:

You have a full backup that kicked at 7:00 pm last night. You have to restore the data in the database to 7:00 pm last night. Can you do it with that full backup file? If so, why does it work? If not, why not? And how do you get it back to that 7:00 pm state?

The "why" bit is the most interesting part. I like a candidate that gets the answer wrong, but with the right reasoning about the log, better than one who get's it right but can't explain why. And the worst answer is obviously, "Sure - a full backup just has all the data as of the time it started!"

What's your favorite question?

Published Monday, November 01, 2010 12:26 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Merrill.

Good question, If you do not mined I will add to my list to interview the candidates.

/*

You connected to SQL Server instance with SA login and run

BACKUP DATABASE dbname TO DISK ='C\dbname.bak'

Why do you get the below error message?

Cannot open backup device 'c:\dbname.bak'. Operating system error 5(error

not found).

*/

November 2, 2010 12:50 AM
 

James Lean said:

Hi Merrill,

So what's the answer?!

I would have said you cannot restore the db to 7pm using *just* that full backup, as it will be restored to the state of when the backup completed.  But I am interested to know whether I have missed something and there *is* a way?

November 3, 2010 1:35 PM
 

Andrew Vogel said:

My favorite question for interviews involves isolating a problem in a 3 tier system.  

--

Extranet users get a white page in their browser after clicking "login".  Once this problems starts, it slowly affects all extranet users.  You have a webserver frontend, middle tier app server, and backend database server.  You have a custom admin application that connects directly to the middle tier app server.

--

Being able to narrow down the issue using different credentials from different angles, then ultimately narrowing down the issue to a web service memory leak is the way to go.  My biggest goal in asking this was to see how a person thinks, not necessarily what they know about said product, which is critical in our environment.

November 3, 2010 7:40 PM
 

merrillaldrich said:

@James - I think the simple answer is no :-).

A backup is an online operation, thank goodness, so by necessity we have to assume the data pages are changing as the backup runs. It follows that the resulting backup file must contain "old" data pages and "newer" data pages in it, that were copied into the file at different times and represent different states. Different times = different log sequence numbers in SQL Server. In order to then restore to a consistent state, the data pages across the whole database, at the end of the restore process, have to be somehow resolved to the same LSN/moment in time. In order to do that, a portion of the log is also copied into the full backup file, and that slice of log is used to perform a part of the recovery process, which modifies any "old" pages in the backup file, which were written early in the backup process, bringing them up to the same instant of time as the "newest" page - the time of the completion of the backup.

Another way to look at it, if I even understand this right: the log, technically called "write-ahead log" - has to contain records as of an equal or later time than the latest page of data, in order for it to be possible to recover to a valid state. If there were data pages that are newer than the available log records, one could not know how to handle them, because the log would have no record about how they came into that state. It would be as if they came from the "future."

That is, the max LSN in the log file must be >= the max LSN represented on any data page in the whole database. Because the data is changing while a full backup executes, one has to assume that the maximum LSN represented in all the data pages in the backup file is the LSN exactly at the moment of the end of the backup execution. That means the log used to recover the database has to also run up to that moment. Hence it's impossible to recover to any state prior to the end of the backup, because the data file would contain mystery "future" pages, impossible to resolve.

Once the full backup is restored, however, it's in a consistent state as of the last moment of the backup execution and, going forward from that point, log backups can be restored up to any specified point in time - because the log is always "ahead" of the entire set of data pages and can accurately reproduce all changes to any piece of data.

There's good info about this here:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/10/26/fuzzy-backups-and-restore-database-with-stopat.aspx

and

http://connect.microsoft.com/SQLServer/feedback/details/613644/restore-database-command-with-stopat-does-not-serve-the-actual-purpose

November 4, 2010 12:45 AM
 

merrillaldrich said:

Hey Andrew - good one. But for a DBA? Actually we had one of our integration people drill a DBA candidate about a whole external load-balanced web service issue similar to that. He did well, luckily, but I kind of felt like it was a long way from DBA-land :-)

November 4, 2010 12:49 AM
 

Andrew Vogel said:

For a DBA, not the same question, but the concept can be abstracted to the appropriate level, say for example permission chains or troubleshooting HA topology.

The context I was originally asked the question in was for an BI support role (Not application infrastructure).  Most candidates blamed the database first because of the context where the question was asked.

November 10, 2010 2:05 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement