SQL Server DBA Backup and Recovery Interview Question Print E-mail
Thursday, 11 December 2008 13:55

SQL Server DBA Backup and Recovery Interview Questions

Written By: Edgewood Solutions Engineers -- 6/11/2008 -- 0 commentsGet our newsletter!         

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 backup and recovery. To help gauge your skills, the question is available to read, but the answer is intentionally hidden. Once you have thought about your answer, highlight the answer and see how you have done. 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 based on 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.
    • 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 2: True or False - The native SQL Server 2005 backups are in clear text.
  • 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.
    • Ensure the long term backup policy meets all industry and regulatory requirements based on the organization.  This could be as long as seven years.
    • 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 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.
  • 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 5: Explain how you could automate the backup and restore process?

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: How can full backups be issued without interrupting the LSN's?
  • Question 4: How is a point in time recovery performed?
    • 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 theMSSQLTips.com Interview Question tips.
  • 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 below.

< Prev   Next >