SQL Server DBA Interview Questions for Backups and Restores

By: Joe Gavin   |   Updated: 2022-08-04   |   Comments (6)   |   Related: More > Professional Development Interview Questions DBA

Problem

Are you a candidate to be interviewed for a SQL Server DBA position or are you the one interviewing a candidate for a SQL Server DBA role? Interviews can be intimidating on both sides. However, there are a few questions an interviewee could expect and that an interviewer can ask to assess basic knowledge of database administration backups and the way the interviewee approaches problem-solving and problem avoidance.

Solution

We'll look at several interview questions and answers related to backing up and restoring SQL Server databases.

SQL Server DBA Interview Questions and Answers

Q: What is the DBA's most important responsibility?

A: A DBA can have multiple responsibilities, but the most important is protecting the business's data.

Q: What are the three main types of database backups?

A:Full, Differential, and Transaction Log.

Q: What are the differences between the three types of backups?

A: As the name implies, a full backup backs up the entire database. A differential backup backs up all changes made since the last full backup. A transaction log backup backs up the serial records of data modifications since the previous transaction log backup.

Q: Given the following backup schedule for a particular day, which backup file(s) do I need to restore to bring the database back to:

• The latest point in time?
• 7:30 PM?
• 12:15 AM?
• 12:05 AM?
• Latest point in time if MyDatabase_FullBackup_1.bak is bad or missing?
• Latest point in time if MyDatabase_DifferentialBackup_4.dif is bad or missing?

A:

Latest point in time

1. MyDatabase_FullBackup_1.bak
2. MyDatabase_DifferentialBackup_4.dif
3. MyDatabase_TranLogBackup_24.trn

7:30 PM

1. MyDatabase_FullBackup_1.bak
2. MyDatabase_DifferentialBackup_3.dif
3. MyDatabase_TranLogBackup_18.trn
4. MyDatabase_TranLogBackup_19.trn

12:15 AM

1. MyDatabase_FullBackup_1.bak
2. MyDatabase_DifferentialBackup_4.dif

12:05 AM

1. MyDatabase_FullBackup_1.bak
2. MyDatabase_DifferentialBackup_3.dif
3. MyDatabase_TranLogBackup_18.trn
4. MyDatabase_TranLogBackup_19.trn
5. MyDatabase_TranLogBackup_20.trn
6. MyDatabase_TranLogBackup_21.trn
7. MyDatabase_TranLogBackup_22.trn
8. MyDatabase_TranLogBackup_23trn
9. MyDatabase_TranLogBackup_24.trn WITH STOPAT 12:05

Latest point in time if MyDatabase_FullBackup_1.bak is bad or missing

1. Only option would be to restore the previous day's full backup and subsequent differential and transaction log backups

Latest point in time if MyDatabase_DifferentialBackup_4.dif is bad or missing?

1. MyDatabase_FullBackup_1.bak
2. MyDatabase_DifferentialBackup_3.dif
3. MyDatabase_TranLogBackup_18.trn
4. MyDatabase_TranLogBackup_19.trn
5. MyDatabase_TranLogBackup_20.trn
6. MyDatabase_TranLogBackup_21.trn
7. MyDatabase_TranLogBackup_22.trn
8. MyDatabase_TranLogBackup_23trn
9. MyDatabase_TranLogBackup_24.trn (ignore MyDatabase_DifferentialBackup_4.dif)

Q: What are the different database recovery models? What is the difference between them?

A:

1. Full
   1. Requires transaction log backups be taken, or transaction log will grow indefinitely
2. Simple
   1. Transaction log not backed up
   2. Transaction log is cleared when SQL Server runs a checkpoint (approximately every minute)
3. Bulk-Logged
   1. Requires transaction log backups be taken
   2. Minimally logs bulk operations

Q: What would you do to fix a transaction log that's grown out of control dramatically due to a database being in Full Recovery Model, but no transaction log backups are taken?

A:

1. Put the database in Simple Recovery Model
2. Run checkpoint (or let the SQL Server do it)
3. Shrink the transaction log file
4. Put the database back in Full Recovery Model
5. Take a full backup of the database
6. Schedule transaction log backups
7. Find out how this happened

Q: In the out-of-control transaction log scenario, what would you do if point-in-time recovery is not needed, i.e., a test database?

A: Leave the recovery model in Simple

Q: Why would you not backup databases to the same drives as your data files?

A: In the event of an unrecoverable storage failure, you would lose both data, logs, and backups. Also, you would be taking I/O away from data and log files.

Q: What is an LSN?

A: LSN, or Log Sequence Number, is a unique number used to track the sequence of the transaction log records in a database.

Q: Can an individual data page be restored? If so, how?

A: Yes. By performing a database restore using 'PAGE ='

Q: What option do I need to restore a database from the backup of another database, i.e., migrating a database or refreshing a dev/test database?

A: WITH REPLACE

Q: What is the restore option 'WITH MOVE' do?

A: It allows a restore of a database with the files in a different location than the original.

Q: How would you restore a database backed up on a SQL Server of a later version than the one you're restoring it on?

A: This is a trick question. You can't. You can only restore a database backup to the same or later version of SQL Server. The only option would be to extract the DDL, run it in the new database, then export and import each table.

Q: In addition to user databases, do you need to backup master, model, msdb, and tempdb?

A:

• Master? Yes. It holds SQL Server-specific information and is critical to the SQL Server's operation.
• Model? Yes. As the name implies, it's the model for any new databases, including tempdb.
• Msdb? Yes. It's where all backup and SQL Agent jobs and other important system-related data are stored.
• Tempdb? No. It gets dropped and recreated fresh every time the SQL Server is restarted.

Next Steps

We've seen a few questions and answers that you can ask or be asked in the DBA hiring process to establish fundamental database backup and restore knowledge. Here are some additional links that will provide more information to answer these questions:

• SQL Server Backup Options and Commands Tutorial
• How to Monitor Backup and Restore Progress in SQL Server
• SQL Server Backup and Restore History Queries
• SQL Server Restore Database Options and Examples
• Script to Retrieve SQL Server Database Backup History and No Backups
• Simple Script to Backup all SQL Server Databases
• How to Migrate a SQL Server Database to a Lower Version
• Backup and Restore SQL Server Databases Programmatically with SMO
• SQL Server Restore Tips
• SQL Server Backup and Restore to Network Drive
• SQL Server Database RESTORE WITH MOVE or not WITH MOVE
• Getting Exclusive Access to Restore SQL Server Database
• Differential Database Backups for SQL Server

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips