SQL Server DBA Backup and Recovery Interview Questions

By:   |   Updated: 2012-01-28   |   Comments (19)   |   Related: More > Professional Development Interview Questions DBA


Problem

If you are preparing for a SQL Server DBA interview as the interviewer or interviewee, today's tip should offer value to you. This tip has interview style questions graded as either easy, moderate or advanced related to SQL Server backup and recovery. Check out the questions and good luck!

Solution

Question Difficulty = Easy

  • Question 1: How does the database recovery model impact database backups?
    • First the database recovery model is responsible for the retention of the transaction log entries.  So the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.
    • Here are the SQL Server database recovery models:
      • Simple - Committed transactions are removed from the log when the check point process occurs.
      • Bulk Logged - Committed transactions are only removed when the transaction log backup process occurs.
      • Full - Committed transactions are only removed when the transaction log backup process occurs.
    • Additional information:

 

 

  • Question 3: How can I verify that backups are occurring on a daily basis?

 

  • Question 4: How do you know if your database backups are restorable?

 

  • Question 5: From a best practices perspective, what is your backup retention policy?
    • Store as many backups locally on the network as would need to be restored to a standby server.
    • Do not store the backups and online databases on the same disks.  If a disk failure occurs, you could lose both backups and online backups with a single failure.
    • Ensure the long term backup policy meets all industry and regulatory requirements based on the organization.  This could be as long as seven years in some industries.
    • Additional information:

Question Difficulty = Moderate

  • Question 1: What are some common reasons why database restores fail?

 

  • Question 2: How can you be notified if a native SQL Server database backup or restore fails via the native tools?

 

 

  • Question 4: What are some common post restore processes?
    • Sync the logins and users
    • Validate the data is accurate
    • Notify the team\user community
    • Cleanse the data to remove sensitive data i.e. SSN's, credit card information, customer names, personal information, etc.
    • Change database properties i.e. recovery model, read-only, etc.
    • Additional information:

 

Question Difficulty = Difficult

  • Question 1: What is the database that has the backup and restore system tables?  What are the backup and restore system tables?  What do each of the tables do?
    • The MSDB database is the database with the backup and restore system tables.
    • Here are the backup and restore system tables and their purpose:
      • backupfile - contains one row for each data file or log file backed up
      • backupmediafamily - contains one row for each media family
      • backupmediaset - contains one row for each backup media set
      • backupset - contains one row for each backup set
      • restorefile - contains one row for each restored file
      • restorefilegroup - contains one row for each restored filegroup
      • restorehistory - contains one row for each restore operation
    • Additional information:

 

  • Question 2: For differential backups, how is the data determined for those backups?

 

  • Question 3: In a situation with full, differential and transaction log backups being issued for a database, how can an out of sequence full backup be issued without interrupting the LSN's?

 

  • Question 4: How is a point in time recovery performed independent of a server down situation?
    • It depends on which backup types are issued.  In this example let's assume that full, differential and transaction log backups are issued.
      • Restore the most recent full backup with the NORECOVERY clause
      • Restore the most recent differential backup with the NORECOVERY clause
      • Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup
      • Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied
    • Additional information:

 

  • Question 5: What are your recommendations to design a backup and recovery solution?
    • Determine What is Needed
    • Recovery Model
    • Select Backup Types
    • Backup Schedule
    • Backup Process
    • Document
    • Backup to Disk
    • Archive to Tape
    • Backup to Different Drives
    • Secure Backup Files
    • Encrypt or Password Protect Backup Files
    • Compress Backup Files
    • How Much to Keep on Disk
    • Online Backups
    • Run Restore Verifyonly
    • Offsite Storage
    • Additional information:
Next Steps
  • As you prepare for an upcoming SQL Server DBA technical interview, review the SQL Server interview questions in this tip as a means to prepare for the technical portion of the interview.
  • As you continue to prepare for the interview, check out all of the MSSQLTips.com Interview Questions:
  • Stay tuned for future SQL Server interview questions related to core SQL Server features. If you have some key interview questions related to backup and recovery that you always include in your interview process, please share your knowledge with the community by posting the questions in the forum.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-01-28

Comments For This Article




Saturday, October 12, 2013 - 4:42:54 PM - Jeremy Kadlec Back To Top (27133)

Brandie and Jeff,

Thank you both for your comments.  I plan on revisiting this tip sometime soon.

Thank you,
Jeremy Kadlec
Community Co-Leader

Saturday, October 5, 2013 - 4:01:18 PM - Jeff Moden Back To Top (27053)

On question 4 - Moderate... anyone that would have to cleanse SSNs, credit cards, or other such personal information after a restore should be fired for not insisting on proper encryption. ;-)  And everyone complains about the NSA snooping on telephone info.  ;-)

Thursday, October 3, 2013 - 8:05:04 AM - Brandie Tarvin Back To Top (27024)

Just an FYI from personal experience...

On Question 4 (Easy), there are rare instances where the VerifyOnly restore will return valid but the backup is still bad. I'm not completely sure why this happens, but it does. So relying on VerifyOnly can potentially cost a DBA her job if the crisis is bad enough. The only true 100% way of knowing your backups are good are to restore them to a sandbox server / another instance on a regular basis.

Question 5 (Moderate), I would never hire a DBA who provided the answer you posted here. Backups can be automated via schedule jobs using the SQL Server Maintenance Plans or via a third party tool. I would never allow anyone to use a cursor for database backups in my environment. If they want to code it via T-SQL, then they code each backup individually. And restores should be done via SQL Agent Jobs (one job per database). Again, no cursors.

 

Thursday, October 3, 2013 - 7:53:49 AM - Brandie Tarvin Back To Top (27023)

"Question 1: How does the database recovery model impact database backups?

  • First the database recovery model is responsible for the retention of the transaction log entries.  So the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution."

This phrasing is wrong in so many ways, it's going to get people who don't know any better into trouble. Recovery Model does NOT dictate retention. It does NOT dictate transaction log backup times. Retention is dictated by the needs of the business and can even be a legal issue, but it has nothing to do with Recovery Model. Backup times are dictated by the needs of the business and the Recovery Strategy, not by the Recovery Model. In fact, even Recovery Model itself is dictated by the needs of the business (how much data they can afford to lose) and the Recovery Strategy.

The Recovery Model dictates HOW data is logged to the Transaction Log. Does it just point to pages and extents? Does it fully log every detail? Can you read the Transaction Log and use it in recovery scenarios? All of these are dictated by Recovery Model. But not the items indicated by article's answer.

Saturday, February 23, 2013 - 8:21:36 AM - chandra Back To Top (22372)

Hi Jeremy,

 

This  is Chandra...Your posts are very useful and valuable.and could you please help me,can you post all types of questions related to SQL DBA which is required for 3+years of experience from basics to till performacne tuning..plz...

 

you can reach me at      [email protected]

 

 

Thanks & Regards,

Chandra

Thursday, August 2, 2012 - 9:24:13 AM - Jeremy Kadlec Back To Top (18885)

Rick,

Thank you for the feedback.  Yes - You are correct.  Every environment is different.

Thank you,
Jeremy Kadlec

Thursday, August 2, 2012 - 7:27:30 AM - Rick Back To Top (18881)

Jeremy,

3Rd party backup tools might dictate some of the options for quastion Difficult/3 & 5. For instance, backing up to disk might not be necessary / wanted, to keep in line with the rest in IT who backup to/with a backup server, such as Microsoft's Data Protection Manager or Tivoli Data Protection. So you might not have the option to backup to disk to keep in line with the rest of the IT department...

Unfortunately I cannot fully test what happens with the LSN in this case if you backup without the 'WITH COPY_ONLY' option as the backup team have the backupserver under maintenance at the moment...

Cheers,,
Rick

 

 

Monday, July 30, 2012 - 8:49:29 AM - Jeremy Kadlec Back To Top (18850)

Dinesh,

I am not sure I understand your question 100%, but I think you are asking about transaction log backups.  Depending on your needs, you could issue full backups once a day, differential backups at key points in time based on the business needs and transaction log backups every minute to every few minutes.  Here is some information to check out:

 

SQL Server Backup Tutorial

SQL Server Restore Tutorial

 

Simple script to backup all SQL Server databases

 

Differential Database Backups for SQL Server

 

Automating Transaction Log Backups for All SQL Server Databases

Auto generate SQL Server restore script from backup files in a directory

Auto generate SQL Server restore scripts after each backup completes

 

HTH.

Thank you,
Jeremy Kadlec

Friday, July 27, 2012 - 3:27:51 PM - Dinesh Dattatray Vishe Back To Top (18832)

Online database is updating for every minute.What backup techinque used ???

Thursday, June 28, 2012 - 9:36:53 AM - Dinesh Vishe Back To Top (18250)

thank U Lot...

Thursday, June 28, 2012 - 9:06:44 AM - Jeremy Kadlec Back To Top (18248)

Dinesh,

You could issue one of the following commands:

HTH.

Thank you,
Jeremy Kadlec

Thursday, June 28, 2012 - 2:51:09 AM - Dinesh Vishe Back To Top (18239)

How take single table backup  in mssql ???

Monday, February 6, 2012 - 10:32:25 AM - Jeremy Kadlec Back To Top (15910)

jrara,

I have updated the question.  Please let me know if it makes more sense now.

Thank you,
Jeremy Kadlec

Monday, February 6, 2012 - 10:28:35 AM - Jeremy Kadlec Back To Top (15908)

jrara,

Thank you for the feedback.  Let me try to clarify that question a little bit more.

I have been in some circumstances where a tail log backup is possible and other times when it is not.

I will re-post the question and ask you to let me know if it makes more sense.

Thank you,
Jeremy Kadlec

Sunday, February 5, 2012 - 6:54:34 AM - jrara Back To Top (15906)

Question 4: How is a point in time recovery performed?

I thought the first thing to do is to take a tail log backup?

Tuesday, January 31, 2012 - 7:57:22 AM - Jason Back To Top (15850)

Jeremy,  Yes, the wording of the question is better.  Thanks!

Monday, January 30, 2012 - 11:10:13 AM - Jeremy Kadlec Back To Top (15837)

Jason,

I have updated the question.  Please let me know if this makes more sense.

Thank you,
Jeremy Kadlec

Monday, January 30, 2012 - 10:55:01 AM - Jeremy Kadlec Back To Top (15835)

Jason,

Thank you for the post and URL. Maybe I need to change the question a little bit, but the scenario I am referencing is outlined in this tip - http://www.mssqltips.com/sqlservertip/1075/copyonly-backups-with-sql-server-2005/. Although this was originally written for SQL Server 2005, I have worked through the four sets of code and the examples remain accurate based on this build:

"Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)"

In my test from the aforementioned tip, the database recovery model is full, the database compatibility level is 100 and no non-logged operations were issued.

I think here are some test cases and the associated status:

·          Full and transaction log backups

o    Full backups can be issued without the copy_only option and you are still able to restore the transaction logs in sequence

o    Full backups can be issued without the copy_only option and transaction log backups can be issued with the copy_only option to skip restoring specific transaction logs

·          Full, differential and transaction log backups

o    Once differential backups gets introduced into the equation then using the copy_only option with the full backup is necessary to issue an out of schedule backup without disrupting the process

Let me take a look at that question again and see if I can further clarify it. If you have a minute run through the example (http://www.mssqltips.com/sqlservertip/1075/copyonly-backups-with-sql-server-2005/) I have outlined in the tip and let me know your results.

Thank you,
Jeremy Kadlec

Sunday, January 29, 2012 - 12:10:47 PM - Jason Back To Top (15820)

Difficult level, Question 3, you don't need the "COPY_ONLY" option... a regular full backup will not break the log chain.  It's a common myth that it does.

 

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/26/transaction-log-backup-and-restore-sequence-myths-amp-truths.aspx

Great article, BTW!














get free sql tips
agree to terms