SQL Server DBA Interview Questions for Backups and Restores

By:   |   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?
Backup File Name Backup Type Backup Time
MyDatabase_FullBackup_1.bak Full 01:00 am
MyDatabase_TranLogBackup_1.trn Transaction Log 01:30 am
MyDatabase_TranLogBackup_2.trn Transaction Log 02:30 am
MyDatabase_TranLogBackup_3.trn Transaction Log 03:30 am
MyDatabase_TranLogBackup_4.trn Transaction Log 04:30 am
MyDatabase_TranLogBackup_5.trn Transaction Log 05:30 am
MyDatabase_DifferentialBackup_1.dif Differential 06:15 am
MyDatabase_TranLogBackup_6.trn Transaction Log 06:30 am
MyDatabase_TranLogBackup_7.trn Transaction Log 07:30 am
MyDatabase_TranLogBackup_8.trn Transaction Log 08:30 am
MyDatabase_TranLogBackup_9.trn Transaction Log 09:30 am
MyDatabase_TranLogBackup_10.trn Transaction Log 10:30 am
MyDatabase_TranLogBackup_11.trn Transaction Log 11:30 am
MyDatabase_DifferentialBackup_2.dif Differential 12:15 pm
MyDatabase_TranLogBackup_12.trn Transaction Log 12:30 pm
MyDatabase_TranLogBackup_13.trn Transaction Log 01:30 pm
MyDatabase_TranLogBackup_14.trn Transaction Log 02:30 pm
MyDatabase_TranLogBackup_15.trn Transaction Log 03:30 pm
MyDatabase_TranLogBackup_16.trn Transaction Log 04:30 pm
MyDatabase_TranLogBackup_17.trn Transaction Log 05:30 pm
MyDatabase_DifferentialBackup_3.dif Differential 06:15 pm
MyDatabase_TranLogBackup_18.trn Transaction Log 06:30 pm
MyDatabase_TranLogBackup_19.trn Transaction Log 07:30 pm
MyDatabase_TranLogBackup_20.trn Transaction Log 08:30 pm
MyDatabase_TranLogBackup_21.trn Transaction Log 09:30 pm
MyDatabase_TranLogBackup_22.trn Transaction Log 10:30 pm
MyDatabase_TranLogBackup_23trn Transaction Log 11:30 pm
MyDatabase_DifferentialBackup_4.dif Differential 12:15 am
MyDatabase_TranLogBackup_24.trn Transaction Log 12:30 am

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:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-08-04

Comments For This Article




Monday, August 22, 2022 - 10:07:20 AM - Fathi Back To Top (90397)
Thank you

Thursday, August 4, 2022 - 2:39:03 PM - Tina Arroyo Back To Top (90345)
Nice! It's good to learn something new. Thank you Joe!

Thursday, August 4, 2022 - 1:50:30 PM - Greg Robidoux Back To Top (90343)
Joe, I updated the article.

-Greg

Thursday, August 4, 2022 - 12:50:28 PM - Joe Gavin Back To Top (90342)
Tina and Sven, thanks for catching that. To restore to 12:05 am we would restore:

MyDatabase_FullBackup_1.bak Full
MyDatabase_DifferentialBackup_3.dif
MyDatabase_TranLogBackup_18.trn
MyDatabase_TranLogBackup_19.trn
MyDatabase_TranLogBackup_20.trn
MyDatabase_TranLogBackup_21.trn
MyDatabase_TranLogBackup_22.trn
MyDatabase_TranLogBackup_23trn
MyDatabase_TranLogBackup_24.trn WITH STOPAT 12:05

Thursday, August 4, 2022 - 12:05:16 PM - Tina Arroyo Back To Top (90341)
Hi Joe,

Thanks for the post. I enjoy reading it. BTW, I think your answer to the restoring the db to 12:05 AM is not accurate. It's not possible to recover up to 12:05 AM because the differential took place at 12:15 AM. Perhaps, if the question was at 12:25 AM then the answer would be to use the 'WITH STOPAT 12:05' option.
Cheers!
Tina

Thursday, August 4, 2022 - 11:36:51 AM - SoehnelS Back To Top (90339)
Hi,

I am not sure, if the answer is right:
12:05 AM

MyDatabase_FullBackup_1.bak
MyDatabase_DifferentialBackup_4.dif

Now you are already at 12:15 AM, or not?
Shouldn't be the *_3.dif and the following trn files 18...23 be used instead before the last step?

MyDatabase_TranLogBackup_24.trn using a 'WITH STOPAT 12:05'

Thanks in advance!

Sven














get free sql tips
agree to terms