Search This Blog

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!
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: