February 3, 2012 at 6:31 am
hello experts,
i have been asked this question many times in many interviews what happenes internally when we take a backup of a database, i dont understand what to answer then ... and what he is actually asking ... can any one let me know what happes ...
thanks..
February 3, 2012 at 6:44 am
Zeal-DBA (2/3/2012)
hello experts,i have been asked this question many times in many interviews what happenes internally when we take a backup of a database, i dont understand what to answer then ... and what he is actually asking ... can any one let me know what happes ...
thanks..
Just posting for conversations sake as I'm not an expert.
One direction you could take is to hit google and have an express version of sql server handy to play with.
Some example questions I can imagine:
"are there records of backups kept in the database? are there sections of storage marked as backed up? how does a full backup affect a following incremental backup? how does a transaction log backup affect a database's transaction log? what units are measurable as being used in a transaction log?
one example google search: sql backup internals
brought up this page for instance:
http://www.dbaglobe.com/2011/01/sql-2008-mcm-studies-backup-internals.html
February 3, 2012 at 7:10 am
There's pretty good information on backups (and everything else) in Kalen Delaney's excellent book SQL Server 2008 Internals. It's really my favorite reference after Books Online. I'd suggest reading up in those two areas so that you can answer this question.
I do lots of interviews (well, I used to, not so much with the new job). This type of question is meant to test your understanding of how SQL Server works. I'm not going to provide you with the answer, but I am going to tell you what I'd talk about in answering this question. First, I'd explain exactly what a full backup is. I'd also mention that there are differential, file/filegroup, and partial backups. I'd also mention log backups. Then I'd talk about recovery models. Then I'd talk about how the backup process deals with transactions that occur while it runs. Finally I'd talk about what happens at the end of a backup. If they still don't look happy I'd start talking about how the other types of backups work in relation to the full backup.
As the interviewer, I wouldn't expect you to get every term correct (heck, I won't), but I'd want to see the concepts explored in a way that shows you understand how SQL Server works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply