SQL Server DBA Backup and Recovery Interview Questions

By: Jeremy Kadlec | Read Comments (7) | Print Jeremy is a co-founder of MSSQLTips, a SQL Server MVP and a regular contributor with over 200 published tips. Related Tips: More

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:
    • Selecting the database recovery model to ensure proper backups
    • SQL Server point in time recovery

• Question 2: True or False - The native SQL Server 2005 backups are in clear text.
  • True - With SQL Server 2008 is the introduction of natively encrypted database backups.
  • Prior to SQL Server 2008 a third party product was necessary to encrypt the database backups.
  • Additional information:
    • SQL Server 2005 Backup Product Options
    • Implementing Transparent Data Encryption in SQL Server 2008
    • Encrypt and safeguard your SQL Server database backups

• Question 3: How can I verify that backups are occurring on a daily basis?
  • Review the SQL Server error log for backup related entries.
  • Query the msdb.dbo.backupset table for the backup related entries.
  • Review the file system where the backups are issued to validate they exist.
  • Additional information:
    • Do you really know if your SQL Server database backups are successful?

• Question 4: How do you know if your database backups are restorable?
  • Issue the RESTORE VERIFYONLY command to validate the backup.
  • Restore the backups as a portion of a log shipping solution.
  • Randomly retrieve tapes from off site and work through the restore process with your team to validate the database is restored in a successful manner.
  • Additional information:
    • Automating the SQL Server RESTORE VERIFYONLY Process with Maintenance Tasks

• 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:
    • SQL Server Database Backup Retention Periods

Question Difficulty = Moderate

• Question 1: What are some common reasons why database restores fail?
  • Unable to gain exclusive use of the database.
  • LSN's are out of sequence so the backups cannot be restored.
  • Syntax error such as with the WITH MOVE command.
  • Additional information:
    • Getting exclusive access to restore SQL Server databases

• Question 2: How can you be notified if a native SQL Server database backup or restore fails via the native tools?
  • Setup SQL Server Alerts to notify SQL Server Agent Operators on a failure condition.
  • Include RAISERROR or TRY\CATCH logic in your backup or restore code to send an email on a failure condition.
  • Additional information:
    • SQL Server Database Backup and Restore Failure Notifications

• Question 3: True or False - All successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single trace flag.
  • True - Just enable the trace flag 3226.
  • Additional information:
    • How to stop logging all successful backups in your SQL Server error logs

• 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:
    • SQL Server 2000 Database Restores Mapping Users to Logins

• Question 5: Explain how you could automate the backup and restore process?
  • Backups can be automated by using a cursor to loop through each of the databases and backup each one
  • Restores can also be automated by either looping over the files, reading from the system tables (backup or log shipping) or reading from a table as a portion of a custom solution
  • Additional information:
    • Simple script to backup all SQL Server databases
    • Auto generate SQL Server database restore scripts

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:
    • Auto generate SQL Server database restore scripts

• Question 2: For differential backups, how is the data determined for those backups?
  • As data is changed in the extent, the extent is marked as changed and the entire extent is backed up.
  • Additional information:
    • Differential Database Backups for SQL Server

• 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?
  • Issue the BACKUP command with the COPY_ONLY option
  • Additional information:
    • COPY_ONLY Backups with SQL Server 2005

• 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:
    • SQL Server point in time recovery

• 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:
    • SQL Server Backup Checklist

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:
  •  SQL Server Developer Interview Questions
  • SQL Server DBA 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.

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Sunday, January 29, 2012 - 12:10:47 PM - Jason
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!

Monday, January 30, 2012 - 10:55:01 AM - Jeremy Kadlec

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

Monday, January 30, 2012 - 11:10:13 AM - Jeremy Kadlec
Jason, I have updated the question.  Please let me know if this makes more sense. Thank you, Jeremy Kadlec

Tuesday, January 31, 2012 - 7:57:22 AM - Jason
Jeremy,  Yes, the wording of the question is better.  Thanks!

Sunday, February 05, 2012 - 6:54:34 AM - jrara
Question 4: How is a point in time recovery performed? I thought the first thing to do is to take a tail log backup?

Monday, February 06, 2012 - 10:28:35 AM - Jeremy Kadlec
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

Monday, February 06, 2012 - 10:32:25 AM - Jeremy Kadlec
jrara, I have updated the question.  Please let me know if it makes more sense now. Thank you, Jeremy Kadlec

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code