In this article, we’ll walk through, some of the refined list of SQL Server backup-and-restore, (or recovery) interview Q&A. Also, it includes the high-level overview and links of the “stairway to backup-and-restore series” articles for detailed information. As you read through the lists of Q&A, you’ll learn most of the features and concepts of Backup-and-Restore operations.
In this article, we’ll discuss the following topics:
- What are database backups?
- What are factors to consider while planning for backup, restore, and recovery strategy?
- What are different stages of data life-cycle management?
- How to do plan or setup SQL Server Backup and Restore strategy in a multi-server environment using native tools?
- How does the database recovery model impact database backups?
- Explain the different types of database backup in SQL Server?/a>
- How can I verify that backups are occurring on a daily basis?
- What is the difference between Database backup and Database snapshots?
- What are the system tables that store backup and restore related information?
- What are the new enhancements added for Backup and Restore SQL Server 2017?
- What is a smart T-Log backup in SQL Server 2017?
- Assume that the database recovery model is full. The full database backup runs every week at 9 PM. Sunday, differential backup runs daily at 9 PM. Monday to Saturday, and hourly transaction log backups. Now, database crashed on Friday 5:10 PM. How to do a point-in-time recovery of the database?
- How to do database refresh automation?
- Explain the process of database backup and restore operations using the Cloud?
- In a given situation, assume that the system has 3 drives with 30 GB of free space on each drive. Now, how can you perform a database backup for 80 GB database?. Is it possible?
- Explain piecemeal restore operation?
- What are database recovery phases and how it is different for in-memory optimized objects?
- How to perform database backup and restore operation on SQL Server Docker containers?
- What are the native toolset that are available to perform database backup and restore operation?
- What are the top 10 trace flags can be used with database backup?
1. What are database backups?
The word “backup” refers to copying and archiving the data so that it may be used to restore in case of an event of data loss. In general, you should back up any work or data that can’t be replaced easily.
A database backup is a copy of data from the database, which can be used to reconstruct the data. A database backup is a process to protect the data from various disasters. Disasters cannot be completely prevented; the least we can do is to ensure we have everything that’s needed to get up and running as soon as we can.
An overview of the process of SQL Server backup-and-restore
2. What are factors to consider while planning for backup, restore, and recovery strategy?
It’s a broad topic to discuss but, some of the high-level points to be considered while defining good backup strategy include the following topics:
- Rate or frequency at which data changes
- Measure the volume of online transaction processing
- Measure the frequency of the schema or structural design changes
- Measure the frequency of database configuration change
- Understand data generation and data loading patterns
- Know the nature of the data
For additional information: refer to the below link
Backup and Restore (or Recovery) strategies for SQL Server database
3. What are different stages of data life-cycle management?
It’s important for any database administrator to understand the data lifecycle and the nature of the particular business, in order to have the ability to recover artifacts of business value from any sort of data disruptions.
- Data capture or generation
- Data usage
- Data corruption
- Data corruption prevention
- Data protection
- Data archival
- Data purging
- Data retention
For additional information: refer to the below link
Understanding the SQL Server Data Management Life Cycle
4. How to do plan or setup SQL Server Backup and Restore strategy in a multi-server environment using native tools?
In some cases, as part of company acquisition process, we may lead to revisit the review the existing the plans or we may also need to run through the steps to measure the current process. It is always advised to understand the importance and implication of business requirements. As each database may have different requirements based on the application it serves. The requirements may be based on:
- How frequently does the application access the database? Is there a specific off-peak period when the backups can be scheduled?
- How frequently does the data get changed? If the changes are too frequent, you may want to schedule incremental backups in between full backups. Differential backups also reduce the restoration time.
- If only a small part of a large database changes frequently, partial and/or file backups can be used.
- Estimate the size of a full backup. Usually, the backup is smaller than the database itself, because it does not record the unused space in a database.
PowerShell comes into the rescue for most of the DBAs. It is really that simple enough to design to gather backup information with few lines of code.
For additional information: refer to the below link
5. How does the database recovery model impact database backups?
Database recovery model is the database configuration determines the type of database backup one could initiate on the database. It defines the state of the entries in the in transaction log files. Also, it provides the ways to restore or recover the database based on the configuration from varieties of failure.
SQL Server database recovery models:
- SIMPLE
- FULL
- Differential
For additional information: refer to the below link.
Understanding SQL Server database recovery models
6. Explain the different types of database backup in SQL Server?
The most common types of backups available in SQL Server:
- Full
- Differential
- Transaction log
- Tail Log backup
There are other backup types available as well:
- Copy-only backup
- File backups
- Partial backups.
For additional information: refer to the below link
Understanding SQL Server Backup Types
7. How can I verify that backups are occurring on a daily basis?
You could rely on database backup reports. In general, database administrators are very much concerned with getting the database backup report on a daily basis and also alerts as per the Service Level Agreement. It’s considered as a prime role of DBAs to rely on the backup report to understand and how the backups are running.
The reports can be generated in many ways
- PowerShell scripts can be used to review the SQL Server error logs for backup failure events for the specific event id 18204,18210,3009,3017,3033, and 3021
- Use T-SQL to query msdb.dbo.backupset for the backup information
- PowerShell SMO library to pull the related backup information
For additional information: refer to the below link
SQL Server Database Backup and Restore reports
8. What is the difference between Database backup and Database snapshots?
The database snapshots are directly dependent on the source structure of the database. Therefore, snapshots can never be a substitute for backup-and-restore strategy. For instance, if an entire database is lost or corrupted, it means, the source files become inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.
For additional information, refer to the below link
Understanding Database snapshots vs Database backups in SQL Server
9. What are the system tables that store backup and restore related information?
The MSDB database in-house several system tables that stores all the information related to the backup-and-restore operation. You can query the following system tables for finding backup-and-restore information.
- backupfile – Store information about all the backup of the data file or log file
- backupfilegroup -This gives an information about all the backed up filegroups
- backupmediafamily – Stores information about each media family
- backupmediaset – Stores information about each backup media set
- backupset – Stores information about each backup set
- restorefile – Stores information about each restored file
- restorefilegroup – Stores information about each restored filegroup
- restorehistory – Stores information about each restore operation
For additional information; refer to the below link
Backup and Restore (or Recovery) strategies for SQL Server database
10. What are the new enhancements added for Backup and Restore SQL Server 2017?
SQL Server 2017, the enhanced the DMV’s and DMF’s which facilitates an extra column to measure the modified extents in a simpler way.
DCM (Differential Changed Map) tracks and tells what pages are changed since the last full backup. The values could be leveraged to determine if you need to initiate a full or differential backup.
For more information, refer to the below link
Smart database backups in SQL Server 2017
11. What is a smart T-Log backup in SQL Server 2017?
In SQL Server 2017, enhancement has been made to the sys.dm_db_log_stats dynamic management function. This function returns a column log_since_last_log_backup_mb. Now, you have a better control the transactional log backup based on the amount of the data that has changed since last transactional log backup.
For more information, refer to the below link
Smart database backups in SQL Server 2017
12. Assume that the database recovery model is full. The full database backup runs every week at 9 PM. Sunday, differential backup runs daily at 9 PM. Monday to Saturday, and hourly transaction log backups. Now, database crashed on Friday 5:10 PM. How to do a point-in-time recovery of the database?
Yes, the point-in-time recovery is possible.
- First, initiate a tail log backup
- Now, restore recent Full database backup with NORECOVERY, i.e.is. Sunday 9 PM backup
- Apply the Thursday night differential backup with NORECOVERY, i.e.is Thursday 9 PM. Differential backup
- Next, Apply all the T-Log backups since Thursday Differential backup with NORECOVERY option
- Apply tail-log backup WITH RECOVERY and STOPAT options
For additional information, refer to the below link
Tail-Log Backup and Restore in SQL Server
13. How to do database refresh automation?
This can be done using several of the following available techniques:
- Sqlcmd, Robocopy utility, and T-SQL
For more information, see the below article
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
- PowerShell
For more information, see the below article
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
- Sqlpackage.exe tool
For more information, see the below article
14. Explain the process of database backup and restore operations using the Cloud?
The backup-to-cloud functionality was added in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences. SQL Server database Backup to Azure Blob Storage is a process designed to perform almost like a backup device, such as disk or tape. During the backup or restore process, a URL is selected as a “device type” which in turn triggers a VDI (Virtual Backup Device Interface) client process. The process acts as an intermediary agent to send the database backup to the Azure Blob Storage.
For more information, refer the below link
SQL Server Database Backup and Restore operations using the Cloud
15. In a given situation, assume that the system has 3 drives with 30 GB of free space on each drive. Now, how can you perform a database backup for 80 GB database?. Is it possible?
Yes, it is possible.
In some instances, we’re limited by the amount of space on the drives. What if we wanted to backup an entire database that is huge? Or what if we have to copy the backup files over the network? It might be a good idea in these cases to split the backup into smaller chunks—each being a separate file.
For more information, refer to the below link
Understanding SQL Server Backup Types
16. Explain piecemeal restore operation?
Piecemeal restore helps with databases that contain multiple file-groups to be restored and recovered at multiple stages. This would give an option to customize the backup and restore (or recovery) solution.
Consider a scenario; where we have a database with 3 file-groups, Primary, read-only and read-write file-groups. We actually need not perform backup for read-only file groups, here we can perform partial backups. We need to have a single backup copy of the read-only file-groups. Using the piecemeal process, we do have the option to restore required file groups but not all of the file groups are required to make the database online at a specific instance. It is always required to restore the primary filegroup but any secondary user-defined file groups are optional, at that point, while doing the restore. After the restore, one could get partial data and it’s available online and for the rest of the data, the users can wait, for a period of time, to recover other file-groups.
Database Filegroup(s) and Piecemeal restores in SQL Server
17. What are database recovery phases and how it is different for in-memory optimized objects?
Database Recovery Phases
When SQL Server instances restart, each database goes through different recovery stages.
The different phases are:
- Analysis
- Redo
- Undo
Analysis: In this phase, the transaction log is analyzed to track the information about the last checkpoint and create the Dirty Page Table (DPT); this captures all the dirty-page details. In In-Memory OLTP engine, the analysis phase identifies the checkpoint inventory and prepares the system table with all the log entries and also its processes the associated file allocation log records
Redo: It’s a roll-forward phase. When this phase completes, the database comes online.
Here are the points to ponder:
- For disk-based tables, the database is moved to the current point-in-time and acquires locks taken by uncommitted transactions.
- For memory-optimized tables, data from the data and delta file pairs are loaded into the memory and then update the data with the active transaction-log based on the last durable checkpoint. During this phase, disk and memory-optimized based object recovery run concurrently.
- In SQL Server 2017, the redo phase of the memory-optimized tables (the associated data and delta files) is done in parallel. This injects faster times for database recovery process.
- When the above operations are completed for both disk-based and memory-optimized tables, the database becomes online and available for access.
Undo: It’s a rollback phase. It holds the list of the active transaction from the analysis phase basically undoing the transactions. This phase is not needed for memory-optimized tables since In-Memory OLTP doesn’t record any uncommitted transactions for memory-optimized tables.
For additional information, refer to the below link
SQL Server Database Recovery Process Internals – database STARTUP Command
For memory-optimized databases, refer the below link
Backup and Restore of a SQL Server database with Memory-Optimized objects TBA
18. How to perform database backup and restore operation on SQL Server Docker containers?
As long as the containers remain intact with the host, the data will remain safe even if the container is stopped or restarted. However, if you remove the container your databases get removed with it and it’ll be gone forever.
Let’s discuss the Ducker’s solution that keeps the data safe across containers. Using Docker data volume (-v) option, it is that simple to share the data. During the SQL Server container creation process, map to the SQL Server database file directory using –v parameter.
For additional information, refer the below link
Backup and Restore using SQL Server Docker Containers TBA
19. What are the native toolset that are available to perform database backup and restore operation?
- SSMS
- Sqlcmd – Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
- Sqlpackage.exe – SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
- SQL Ops Studio – Backup and Restore operations using SQL Ops Studio TBA
- PowerShell – Backup Linux SQL Server databases using PowerShell and Windows task scheduler
20. What are the top 10 trace flags can be used with database backup?
When used the following traceflags with 3605 traceflag, the output message to the errorlog.
- 1806 – Turn off Instant-file-initialization
- 3004 – Logs every step of backup/restore internal operation.
- 3213 – Display the buffer and maxtransfersize information
- 3023 – Enables CHECKSUM option for the BACKUP command
- 3226 – Prevent log backup entries written into the errorlog
- 3001 – Prevent log backup entries written into the MSDB tables.
- 3014 – Log more internal information for every backup operation
- 3042 – Bypasses the default backup compression pre-allocation algorithm
- 3051 – Enables database backup to URL logging
- 3608 – Prevents SQL Server from automatically starting and recovering any database except the master database
Table of contents
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021