SQL Server Backup Interview Questions

By:   |   Comments (2)   |   Related: More > Professional Development Interview Questions DBA


Problem

I am preparing a SQL Server DBA interview question and answer series that will help prepare professionals for basic and advanced questions. In this tip, we will start with the questions and answers related to SQL Server backups for both new and experienced professionals.

Solution

In this tip, let's start with questions related to backups in SQL Server. Before going to an interview, be sure to review the below links as a starting point.

Q1. What is a SQL Server database backup and why are they needed?

Answer: In SQL Server, a database backup is a point in time copy of database data and code that we can use to restore and recover the data in case of disaster, failure or any requirement where the database backup needs to be restored to another environment.

An effective SQL Server database backup strategy is important to recover from any disaster, which could negatively impact the application or business.

Additional Resources:

Q2. How many types of SQL Server database backups are there?

Answer: In SQL Server, we can issue the following types of database backups:

  • Full Backup
  • Differential backup
  • Log Backup
  • Copy-Only Backup
  • Filegroup Backup
  • Partial Backup

Additional Resources:

Q3. What is a full SQL Server database backup and explain the internal process performed?

Answer: A full backup is a complete point in time version of the data and code. It allows the DBA to restore back to that point in time for recovery. Full backups are needed prior to both differential and transaction log backups.

SQL Server follows the below steps during a BACKUP operation:

  • A full database backup forces a database checkpoint to flush all data to disk.
  • The backup process reads the data pages and writes them to the backup file.
  • Once the data reading operation is complete, it reads the Transaction Log.
  • The full database backup does not contain all of the records from the transaction log. Instead, it chooses only those records that are necessary to make the restored copy of the database transaction consistent. Those records begin either from the latest active transaction or from the checkpoint created by the backup.
  • The process writes to the system tables in the MSDB database including the Backup LSN and backup history.

Additional Resources:

Q4. Suppose a SQL Server update transaction started before the full backup, it made changes after the full backup checkpoint and transactions were committed before the full backup. In this case, explain which data would be included in the full backup.

Answer: The full backup includes all transaction log records starting from the latest active transaction. This implies that the full backup includes the whole transaction with all changes that were made after the checkpoint to apply those changes during the database recovery process.

Additional Resources:

Q5. Explain the above scenario, if a transaction committed before starting the full SQL Server database backup.

Answer: In this case, once the full backup initiates the checkpoint process, all the changes made by the transaction were applied with the checkpoint so the backup will contain transaction log records added after the checkpoint.

Additional Resources:

Q6. Which SQL Server database recovery models are supported with a full database backup?

Answer: Full SQL Server database backups are supported by all available recovery models:

  • Simple
  • Full
  • Bulk-logged

Additional Resources:

Q7. What is a SQL Server differential backup?

Answer: SQL Server differential database backups contain only extents that have changed since the last full backup.  The changes are denoted by flipping a bit at the extent level to determine if the extent needs to be backed up or not.

Additional Resources:

Q8. How do SQL Server differential backups work internally?

Answer: SQL Server stores data in 8 KB pages and an extent is comprised of eight pages. Each Global Allocation Map interval (GAM interval) covers 64,000 extents and has a special database page called a differential character map (DCM). The DCM tracks which extent has changed since the last full backup. A SQL Server differential database backup reads all the bitmaps and backs up the data extents that are marked as changed for the differential backup.  These bits are reset once a full database backup is executed.

Additional Resources:

Q9. Which recovery models are supported by SQL Server differential database backups?

Answer: We can issue SQL Server differential database backups with all recovery models:

  • Simple
  • Full
  • Bulk-logged

Additional Resources:

Q10. If you have multiple SQL Server differential database backups, do you need to restore all differential database backups with the full backup?

Answer: No, differential backups contain data all extents that have changed since the last full backup. Therefore, we need to restore the full backup followed by the just the latest differential backup.

Additional Resources:

Q11. Explain a scenario where you can implement a SQL Server differential backup.

Answer: As an example, we have a multi-terabyte database and cannot issue full database backups on a regular basis due to the time needed to complete the full backup and space limitations for large databases.  We can use SQL Server differential backups to save time and storage while being able to have multiple recovery points.

Additional Resources:

Q12. What is a SQL Server log sequence number?

Answer: A log sequence number (LSN) uniquely identifies every record in the SQL Server transaction log.

Additional Resources:

Q13. What is the value for database_backup_lsn in the SQL Server database backup history?

Answer: It is the log sequence number of the most recent full SQL Server database backup. It might correspond with first_lsn as well. The DatabaseBackupLSN of the first full backup is always equal to zero.

Additional Resources:

Q14. Suppose you are running a weekly full backup and daily differential backup, but now you have a requirement for an ad-hoc full backup.  How can you issue an ad-hoc full backup and why?

Answer: We can issue a SQL Server copy only backup in this situation. Copy only backups are used to create a full database without breaking the log chain. We need to add the "COPY_ONLY" clause to the BACKUP DATABASE command for issuing a copy only backup. This copy only backup cannot be used as a starting point for subsequent backups.

Additional Resources:

Q15. How can we perform a SQL Server compressed backup?

Answer: We can perform a SQL Server compressed backup by adding the WITH COMPRESSION clause as shown below.

BACKUP DATABASE [Database Name] To Disk = ‘Destination' WITH COMPRESSION

Additional Resources:

Q16. How do you calculate the SQL Server database compression ratio for a backup?

Answer: We can use the below query to calculate the backup compression ratio: 

SELECT backup_size/compressed_backup_size FROM msdb.dbo.backupset

Additional Resources:

Q17. Is there a way to issue compressed backups for all databases on a SQL Server?

Answer: Yes, we can configure compression at the instance level. By default, compressed backups are disabled at the instance level in SQL Server.

To enable this configuration, we need to set the backup compression default by using the below query:

Exec sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;
GO

Additional Resources:

Q18. Are SQL Server compressed backups resource intensive? Which server configurations need to be checked for optimal backup performance?

Answer: Generally speaking, CPU consumption is higher when a compressed SQL Server backup is issued and might affect the overall server performance.

Additional Resources:

Q19. What are the least permissions required for issuing a SQL Server database backup?

Answer: In order to issue a SQL Server database backup, the minimum permissions are:

  • Server level - PUBLIC ROLE
  • Database level - DB_BACKUPOPERATOR

Additional Resources:

Q20. What happens if we enable SQL Server Trace Flag 3042 for compressed backups?

Answer: SQL Server compressed backups use a pre-allocation algorithm to determine how much space the compressed backup will require and will allocate this space at the start of the backup process. A later backup process might expand or shrink the backup file. This helps SQL Server save the overhead of constantly growing the backup file, but it might require more space for taking backups as in most cases it allocates more space than required.

If we enable trace flag 3042, SQL Server will not pre-allocate space for the backup file. Instead, it grows backup file as required. This is useful with limited disk space.

Additional Resources:

Q21. In my environment, we are issuing SQL Server transaction log backups every 5 minutes for our mission-critical database. When we look at the SQL Server error log, it is flooded with information about every successful backup. Can we do something about this?

Answer: Yes, we can enable trace flag 3226. This trace flag is helpful to suppress entries related to each successful backup in the SQL Server Error Log.

We can enable it using DBCC TRACEON (3226,-1) or adding –T 3226 to the startup parameters.

Additional Resources:

Q22. Can we split SQL Server backups into multiple files and if yes, how is that coded?

Answer: SQL Server allows a backup to be split into multiple files at the same time. This helps by using multiple threads for the backup. We can also split the backup files if one drive has insufficient free space.

In order to issue a split backup, just add destination paths in the backup script as shown below:

BACKUP DATABASE [DBName] TO
  DISK = 'Destination 1'
, DISK = 'Destination 2'
, DISK = 'Destination 3'

For the database restoration as well from the split backups, we need to provide all the backup files locations in the restore database command.

Additional Resources:

Q23. How can we create multiple copies of a SQL Server backup?

Answer: We can use mirrored database backups to create multiple copies of the file to different locations. You can specify the mirror backup destination using the MIRROR TO DISK option. This option is available only in the Enterprise edition of SQL Server.  Here is a sample script:

BACKUP DATABASE [DBName] TO DISK = ‘destination'  
MIRROR TO DISK =  ‘Mirror backup destination path'

Additional Resources:

Q24. What is a SQL Server partial backup?

Answer: With a partial SQL Server backup, we can perform a backup of a read-write file group and exclude read-only file groups. Partial backups can be issued for both full and differential backups.

The backup type for partial backup shows the below status in the backupset table of the msdb database:

  • P = Partial
  • Q = Differential partial

Q25. What is a SQL Server transaction log backup?

Answer: A SQL Server transaction log backup contains all transaction log records that have occurred since the last transaction log backup or after the first full backup. It is a consistent backup and allows a point in time recovery of the database. Transaction log backups are incremental in nature. We can only issue transaction log backups with the full and bulk-logged recovery modes.

Additional Resources:

Q26. What is the SQL Server bulk-logged recovery model?

Answer: The SQL Server bulk-logged recovery model allows minimum logging of certain database operations. It is useful to reduce transaction log space for databases with frequent operations such as: SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, REBUILD INDEX, etc.

Additional Resources:

Q27. Can a SQL Server database have point in time recovery when configured with the bulk-logged recovery model?

Answer: Yes you can do point-in-time recovery, but if a minimally logged operation is contained within the last transaction log backup file, you can not do point-in-time recovery using that specific transaction log backup.  You would have to restore the entire transaction log backup.

Additional Resources:

Q28. Suppose someone started a SQL Server database backup for a large database, how can you as an administrator view the backup completion status?

Answer: We can use the SQL Server dynamic management view (sys.dm_exec_requests) to check the backup percentage. It contains the column percent_complete to track the progress of the database backup.  Below is a sample script:

select percent_complete, * 
from sys.dm_exec_requests 
where command like '%BACKUP%'

Additional Resources:

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, July 15, 2022 - 9:03:15 PM - Mark Admiral Back To Top (90269)
"The full backup includes all transaction log records starting from the latest active transaction." In your scenario it would be the log records starting the LSN at Checkpoint and not the LSN of the aforementioned transaction since it was committed (i.e. no longer active) during the backup process. My understanding is that the backup process uses the MIN LSN, which would be MIN(Checkpoint LSN, LSN of last active xact) to determine what log records to include.

Monday, January 14, 2019 - 1:09:39 PM - Gopalakrishnan Arthanarisamy Back To Top (78766)

Hello Rajendra Gupta,

Good series of questions on Backups. Its good for beginners to experts to have glance.

Regards,

Gopalakrishnan Arthanarisamy














get free sql tips
agree to terms