SQL Server DBA Backup and Recovery Interview Question
Written by Administrator
Thursday, 11 December 2008 13:55 - Last Updated Thursday, 11 December 2008 13:58
SQL Server DBA Backup and Recovery Interview Questions
Written By: Edgewood Solutions Engineers -- 6/11/2008 -- 0 comments Get 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:
- 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
1/6
SQL Server DBA Backup and Recovery Interview Question
Written by Administrator
Thursday, 11 December 2008 13:55 - Last Updated Thursday, 11 December 2008 13:58
- 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.
- 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.
- 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:
- 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
2/6
SQL Server DBA Backup and Recovery Interview Question
Written by Administrator
Thursday, 11 December 2008 13:55 - Last Updated Thursday, 11 December 2008 13:58
- 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 be sent to Operators on a failure condition.
- Include RAISERROR or TRYCATCH logic in your backup or restore code to alert on the
failure.
- 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 teamuser 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
3/6
SQL Server DBA Backup and Recovery Interview Question
Written by Administrator
Thursday, 11 December 2008 13:55 - Last Updated Thursday, 11 December 2008 13:58
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: How can full backups 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?
- 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
4/6
SQL Server DBA Backup and Recovery Interview Question
Written by Administrator
Thursday, 11 December 2008 13:55 - Last Updated Thursday, 11 December 2008 13:58
- 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 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
5/6
SQL Server DBA Backup and Recovery Interview Question
Written by Administrator
Thursday, 11 December 2008 13:55 - Last Updated Thursday, 11 December 2008 13:58
include in your interview process, please share your knowledge with the community by posting
the questions in the forum below.
6/6