Prashanth Jayaram

SQL interview questions on database backups, restores and recovery – Part III

July 25, 2018 by

So far, we’ve discussed a lot about database backup commands. In this article, we’ll discuss more on database restore or database recovery processes. When you initiate a restore process, it undergoes a series of internal stages to restore or recover the data to the specific point of time.

Introduction

In this article, you’ll see the FAQs and answers about the database restore and recovery internals. To learn a lot about SQL Server database backup-and-restore (recovery) internals, you can refer the full list of topics at the bottom.

In this article, we’ll answer the following topics.

  1. Define database restore
  2. Define the database recovery process
  3. Check the state of the backup file
  4. Check the number of file(s) in a backup file
  5. Identify the database version from a backup file
  6. Check the backup software tools used for backup
  7. Perform simple and multiple ways to restore a database
  8. Explain WITH OVERWRITE option
  9. Explain WITH NORECOVERY option
  10. Restore differential or t-log backup file
  11. Understand Other restore types STANDBY/READONLY
  12. UExplain WITH REPLACE option
  13. Explain WITH MOVE option
  14. Restore the database using a Split files
  15. Detail Piecemeal restore process
  16. Explain Point-in-time recovery
  17. Describe the Page-Level-Restore process
  18. Explain Recovery-Only database restore
  19. Explain WITH STOPAT option
  20. Generate restore data commands using dynamic T-SQL

Questions

In this section, let us deep-dive into the concepts of the database restore options.

1. What is a database restore?

It is the process of reconstructing the data to a usable state from database backup files in order to facilitate database operations.

2. What do you mean by database recovery?

Database recovery is the process of reconstructing the data that has been lost or it may be due to human errors (accidentally deletion) or hardware corruption or catastrophic failure made the data inaccessible. The data recovery typically refers to the restoration of data to a point where there is no or minimal data loss.

3. How do you check the backup file is a usable state?

The RESTORE VERIFYONLY command is used to check or validate the backup and it will ensure that the backup file is readable form.

4. How do you find the how many files are there .bak\trn files?

The RESTORE FILELISTONLY command is used to list all the files related to the specified backup file.

5. How do you find the database version of SQL Server by using the backup file?

The RESTORE HEADERONLY command output the header information of the backup.

6. How do you find software that was used to create the database backup?

The RESTORE LABELONLY command output the backup media information. In the output, we can see a software column.

7. How do you do a simple database restore in SQL Server?

To restore a simple database from a backup file, run the following command

The different ways to perform database restore operations:

  • Using T-SQL, the database restore commands are almost similar to backup database commands. To perform database restore, you just need to change the word “backup” to “restore” and “to” to “from” followed by restore and recovery options
  • Using SQL Server Management Studio, run through the following steps:
    • Browse Object Explorer
    • Right-click Databases
    • Click Restore Database
    • Select Source in the restore section
    • Select From Device, and choose the browse
    • Click Add to select the backup file
    • Select the Backup set to restore
    • Click the options tab, Enable the checkbox to “overwrite the existing database”
    • Select the recovery state (RECOVERY or NORECOVERY)
    • Click Ok
  • Using PowerShell, this is one of the ways to restore a database in PowerShell
    • Open the PowerShell ISE
    • Load the SQLServer Module
    • Define the variables
      • Database name
      • Backup file location
      • Data file location
      • Log file location
    • Prepare the restore command
    • Invoke the SQL string using Invoke-SQLCmd

8. How do you restore a database from full backup with overwrite option?

The following command is used to restore the database using the specified backup file. If the database already exists, it will overwrite the database files. If the database doesn’t exist, it will create the database and restore the files to the specified location in the backup command.

9. How does a full backup restore allow additional restores such as a differential or transaction log backup?

Using NORECOVERY option, this option leaves the database in a restoring state after the full database restore has completed. This will allow you to restore additional database backup files in order to get the database more current

10. How do you do a differential backup file restore?

To restore a differential backup, the options are exactly the same.

  • You need do a full database restore with the NORECOVERY option
  • Initiate differential restore using the following command
  • Bring the database online, if you’ve no other file to restore.

11. What are other types of database restore that you can perform in SQL Server?

You can think of Standby / Read-Only Restore.

This mode is also known as STANDBY mode and can be used for reading operations.

The standby option is for High-availability setup such as Log Shipping. In this setup, you have configured a warm standby server for disaster recovery. SQL Server engine designed to offer the ability to have the secondary database in a restoring state or in a standby state which allows read-only activity on the secondary database.

12. How does Restore with move option different from normal restore operation?

The RESTORE … WITH MOVE option allows you to specify a new file location for the newly created database. Also, if you are restoring a database from another instance with different file locations, then you need to use this move option.

13. How do you do a restore full database from multiple backup files?

SQL Server supports an option of write the backup data to multiple files. In some cases, the database backup file splitting is to manage the workloads of the system. In this case, it is assumed to be full backup file is available and split into multiple files. The process is similar and we will perform database restore using the following T-SQL:

14. What is Piecemeal restore and explain the internals?

Piecemeal restore is a process to help database restore that contains multiple filegroups (s) and it is recovered in multiple stages.

  • Piecemeal restore process involves a series of the database restore, starting with the Primary filegroup followed by one or more secondary filegroup(s).
  • Piecemeal restore process works with all recovery models
  • The restore process maintains a sequence called partial-restore sequence

See Also,

Database Filegroup(s) and Piecemeal restores in SQL Server

15. What is Point-in-time Recovery?

The RESTORE … WITH STOPAT option allows you to restore your database to a point in time.  This gives you the ability to restore a database prior to an event that occurred that was detrimental to your database.  In order for this option to work, the database needs to be either in the FULL or Bulk-Logged recovery model and you need to be doing transaction log backups.

16. Explain Point-in-time restore with STOPAT option

To perform the point-in-time database restores action, the database recovery model to be in either the Full or Bulk-Logged.

  • First, identify the valid FULL or Bulk-logged backup file
  • Restore the database with NORECOVERY
  • Restore the log

This will restore the database to a point “July 16, 2018, 01:38:00 PM”.

17. What is Page-level restore in SQL Server?

Page-level restore is a process or technique can be used to replace corrupted pages in a database with an uncorrupted data from the database backup file. If you have a corrupt page(s) in SQL Server database, instead of restoring a complete database, you can restore only those pages that are corrupted from the available valid database backup file set. The process can be performed via SSMS or T-SQL. You can query msdb.suspect_pages’ to identify corrupted pages and track the pages that are marked as “suspect” in the table as well.

The entire process is very well explained in the following article

How to perform a page level restore in SQL Server

18. What is WITH REPLACE option in Restore command?

The “RESTORE database…WITH REPLACE” option allows overwriting an existing database while doing a database restore process.

The following restore database command overwrite the database files

19. How do you perform the Recovery-Only database restore?

The scope of recovery-only restore is to the database or file or filegroup(s) level.

Before performing database recovery, restore the entire database make sure it is consistent. However, it is also possible that the recovery can be applied without restoring an entire database. For example, read-only file.

For filegroup, issue the following command

20. How do you generate restore one/one or more/all database script dynamically?

  • In the first T-SQL, the database names and backup file location are passed using a variable.
  • In the following steps, you can customize T-SQL to directly feed all the database names by querying the sys.databases object or you can hard code the database names in the sub-query.

That’s all for now…stay tuned for more updates.

Table of contents

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
517 Views