Prashanth Jayaram

SQL Interview questions on database backups, restores and recovery – Part IV

July 25, 2018 by

In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more.

In this article, we’ll discuss the following topics:

  1. How do you delete six months old data to reduce the size of the backup and restore history tables?
  2. How do you get the Backup History for a specific database including the size, location, and LSN?
  3. How do you create and restore a marked transaction?
  4. How do you find the RESTORE HISTORY of the database?
  5. How do you list the last 30 days restore history at the instance level?
  6. How do you measure the database backup or database restore operation progress?
  7. How do you measure the database growth using backup size?
  8. How do you define or estimate the storage required for database backup?
  9. How do you get most recent database backup time for each database?
  10. How do you get recent database backup time for each database using PowerShell?
  11. How do you get recent database backup time for each database across multiple servers using PowerShell?
  12. How do you find the backup history with duration and compressed backup size columns?

Questions

MSDB database is a log-store and it stores a complete history of all SQL Server backup-and-restore operations.

The following table highlights the high-level detail about the backup-and-restore operation:

System tables Description
backupfile The system table provides the most granular details of the backup file. It stores one row for each data file or log file of a database. The columns describe the file type, file group name, page size and file configuration information.
backupfilegroup
The table in-house the filegroup configuration of the database. It stores one row for each filegroup in a database.
backupmediafamily
It stores a row for each media family.
backupmediaset
It stores a row for each backup media set.
backupset
It contains a row for each backup set for successful backup.
logmarkhistory
Contains one row for each marked transaction that has been committed. It is applicable to only those databases where the recovery model is set to full or bulk-logged.
restorefile
It stores one row for each restored file.
restorefilegroup
It stores one row for each restored filegroup.
restorehistory
It stores one row for each restore operation
suspect_pages It stores one row per page that failed with the error 823 or 824 See the link for more information How to perform a page level restore in SQL Server

1. How do you delete six months old data to reduce the size of the backup and restore history tables?

To reduce the size of the backup and restore history tables, delete the entries of backup sets that are older than the specified date-time. It is recommended to run sp_delete_backuphistory frequently to clean-up the entries from the MSDB database.

If you want to remove all the entries of backup history for a specific database, run sp_delete_database_backuphistory <database name>

2. How do you get the Backup History for a specific database including the size, location, and LSN?

The following T-SQL provides you the backup information and LSN details of a given database.

The LSN is viewable using the following system-tables and using the restore command:

  • backupset
  • backupfile
  • sys.database_files;
  • sys.master_files
  • RESTORE HEADERONLY

In the following example, you can see that the LSN is ordered in a Zig-Zag fashion.

3. How do you create and restore a marked transaction?

Marked transactions are very useful to recover the database to a logically consistent point.

To create a marked transaction and restore the marked transaction follow the steps:

  1. Create full/differential database backup

  2. Use “BEGIN TRANSACTION WITH MARK” clause to mark the transaction and perform the DML operations

  3. Back up the SQLShackDemo transaction-log

  4. To verify the marked transaction, run the following command


  5. Restore full database backup WITH NORECOVERY option.

  6. Restore log WITH STOPATMARK option

  7. Verify the data


4. How do you find the RESTORE HISTORY of the database?

This following T-SQL provides you with information about a particular database with the restore history and source, destination, start, end time and type of the restore operation.


5. How do you list the last 30 days restore history at the instance level?

The following T-SQL provides you a list of last 30 days data of database restore history.

Output:

6. How do you measure the database backup or database restore operation progress?

To measure the backup operation progress or estimate the time and percentage completed, you can query the DMV—sys.dm_exec_requests.

This script provides the output with backup estimation time and percentage completed.

7. How do you measure the database growth using backup size?

You can construct the T-SQL using backup system table to analyze the growth of the database over a given timeframe. The T-SQL script used in the below-mentioned link is used to calculate the capacity planning of the databases. The metrics are useful for capacity planning and forecasting.

Backup and Restore (or Recovery) strategies for SQL Server database

8. How do you define or estimate the storage required for database backup?

You can refer the T-SQL to get very detailed information about the database backup history. It also talks about capturing the baseline database growth metrics.

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

9. How do you get most recent database backup time for each database?

The following T-SQL provides the most recent backup completion time of all databases along with the database name

10. How do you get recent database backup time for each database using PowerShell?

The following PowerShell script provides the most recent backup completion time of all databases with the database name

  1. Load the SQLServer Module
  2. Instantiate the SMO class library
  3. Invoke the database property

11. How do you get recent database backup time for each database across multiple servers using PowerShell?

This can be done using looping construct in PowerShell with very few lines of code.

  1. Load the SQLServer Module
  2. List the SQL Server instances
  3. Use looping construct to Instantiate the SMO class library and list the database properties

12. How do you find the backup history with duration and compressed backup size columns?

The following T-SQL get the latest successful backups and it includes columns such as database name, backup start time, backup end time, a derived column named duration (mins), backup file location, type of the backup, backup size, and compressed backup size (if used)

Output:

That’s all for now…

Wrapping up

Thus far, we’ve covered most of the concepts of “database backup-and-restore” operations. Please refer the TOC for more information.

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


See more

To manage SQL Server backups, consider ApexSQL Backup, a tool that offers automation of backup, restore, and log shipping jobs, stores details of all backup activities and enables easy cross server backup management and maintenance.


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
146 Views