SQL Server Backup & Recovery Interview Questions & Answers

This article is based on SQL Server backup & recovery interview questions and answers. You can access SQL Server Architecture based interview questions and General DBA related interview questions in attached links. Feel free to counter on any point that is not correct or where you are not agree. We will discuss and love to hear your expert advise. You can access All SQL Server Interview Questions and Answers on different topics in attached link.

SQL Server Backup & Recovery Interview Questions

Question- What is Minimal Logging and how is it different from Full Logging?

Answer- Minimal logging involves logging only information that is required to recover the transaction without supporting point-in-time recovery. Under the full recovery model, all bulk operations are fully logged. However, you can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations.

Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.

Question- Can you name few operations that logs Minimally during bulk-recovery model?

Answer- The following operations, which are fully logged under the full recovery model, are minimally logged under bulk-logged recovery model:

  1. Bulk import operations (bcp, BULK INSERT, and INSERT… SELECT).
  2. SELECT INTO operations
  3. CREATE INDEX operations
  4. ALTER INDEX REBUILD or DBCC DBREINDEX operations.
  5. DROP INDEX new heap rebuild

Question- If I change my database recovery model from FULL to SIMPLE, does transactions will be logged in to log file?

Answer- Yes, Transactions will be logged in SIMPLE recovery model as well. The difference is all logged transactions will be cleared during checkpoint operation in this recovery model. Read more about Checkpoint operations & SQL Server Recovery Models.

Question- If yes in above question then how is SIMPLE RECOVERY Model different from FULL Recovery Model?

Answer- All logged transactions will be cleared during checkpoint operation and transaction log backup is not allowed so point-in-time recovery is not possible in SIMPLE recovery model. Whereas transaction backup is allowed in full recovery model and point-in-time recovery is also supported. Logs got cleared only after taking log backup or switching the recovery model to SIMPLE.

Question- Can we achieve point-in-time recovery in Bulk-logged Recovery Model?

Answer- Yes, if there is no bulk operation performed on your database and you have all log backups. Point-in-time recovery is not possible if your recovery point falls falls in-between any bulk operations. .

Question- How differential backup works? or

How differential backup captures only updated data since full backup in its dump file?

Answer- Differential Changed Map is a page type that stores information about extents that have changed since the last full backup. Database engine reads just the DCM pages to determine which extents have been modified and captures those extents in differential backup file.

Question- Why cannot we serve copy-only backup  as a differential base or differential backup?

Answer- The differential changed map page is not updated by a copy-only backup. Therefore, a copy-only backup cannot serve as a differential base or differential backup. A copy-only backup does not affect subsequent differential backups.

Question- Why a database log file is growing like anything that is running in SIMPLE Recovery Model?

Answer- It means some transactions are active and running on your database.  As we know logs are captured in simple recovery model as well so that active transaction is getting logged there. The inactive portion in log file clears during checkpoint operation.

Question- Can we restore a database till the specific time?

Answer- Yes, if database is running in full recovery model and database has log backups till that specific time. We can use STOPAT clause to recover till a specific time.

Question- Suppose we are running Daily full backup at 8PM in night and every half an hour transaction log backup. Now your database is crashed at 3.41PM. How will you recover your database to the point it was crashed?

Answer- Below steps we will perform in a sequence manner to recover this database to the point it was crashed.

  • First we will run tail log backup to capture all transactions that are not captured in previous log backups at the point database was crashed.
  • Restore last night Full backup with NORECOVERY that was taken at 8PM.
  • Apply all transaction log backup since last night full backup with norecovery.
  • Apply tail log backup on the database with recovery and with STOPAT parameter. 

Question- Take the same scenario as above, now you found that there is one log file let’s say at 2 PM got corrupted and not restorable. What will be the impact on your database recovery?

Answer-  We cannot recover this database till that point it was crashed and we would have last data till 1.30PM considering log backup runs on every half an hour.

Question- Suppose we are running Weekly Sunday full backup at 8PM, daily differential backup at 10PM and every half an hour transaction log backup. Now your database is crashed on Saturday 3.11PM. What would be your fastest way to recover this database in point in time?

Answer-  We will perform below steps to recover this database in point-in-time:

  1. Try to run tail log backup at the point database was crashed.
  2. Restore latest Weekly Sunday Full backup with NORECOVERY that was taken at 8PM.
  3. Restore Friday night differential backup with NORECOVERY that was taken at 10PM.
  4. Apply all transaction log backup since Friday differential backup with norecovery.
  5. Apply tail log backup on the database with recovery and with STOPAT parameter. 

Question- In addition to above question, suppose you came to know that Friday night differential backup was corrupted then what would be your strategy to recovery the database in point-in time?

Answer- We will perform below steps to recover this database in point-in-time:

  1. Try to run tail log backup at the point database was crashed.
  2. Restore latest Weekly Sunday Full backup with NORECOVERY that was taken at 8PM.
  3. Restore Thursday night differential backup with NORECOVERY that was taken at 10PM.
  4. Apply all transaction log backup since Thursday night differential backup with norecovery.
  5. Apply tail log backup on the database with recovery and with STOPAT parameter. 

Question- Suppose you came to know that differential backups ran on Monday and Wednesday are corrupted and you have only Tuesday and Thursday differential backups along with full backup and all log backups. Explain your sequence to restore the database?

Answer- We will follow same sequence that we follow in previous question. We will apply weekly full backup then Thursday differential backup along with all transaction log backups.

Question- How will you restore tempdb?

Answer- We should not restore tempdb as it’s a temporary database and created everytime we restart SQL Server service.

Question- What is COPY_ONLY full backup and how it is different from regular full backups?

Answer- Difference between regular full and copy-only full backup is that copy-only full backup does not break the differential chain. Neither of them breaks the log chain as neither of them truncates the log file. A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Please Like, Share and comment us about your feedback. I hope you like this article. You can comment your questions in below section. Please follow our facebook page and Twitter handle to get latest updates.

Read More Interview Q&A:

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

3 Responses

  1. Ganesh S. Jagdale says:

    good questions for last moment preparations.

  2. Ganesh S. Jagdale says:

    good questions and answers be updating…….

Leave a Reply

Your email address will not be published. Required fields are marked *