Sunday, March 24, 2013

SQL Server DBA Backup and Recovery Interview Questions
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!


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.
Posted by at

2 comments:

  1. UnknownJuly 3, 2013 at 2:53 AM

    Nice post very helpful

    dbakings

    ReplyDelete
  2. AnonymousDecember 1, 2015 at 11:28 PM

    SQL server interview questions

    http://winmilestone.com/Topic?Interview

    You can get real interview sql server questions on forums which help me lot in clearing interviews. refer site
    www.winmilestone.com
    interviews questions on forums->interviews->

    ReplyDelete

Subscribe to: Post Comments (Atom)