Prashanth Jayaram

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

July 30, 2018 by

So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore database command gives an option to test the consistency and integrity of the backup file.

As we all know, the backup database command bound with many database options. Indeed, it facilitates the execution of specific backup database command that meets the business requirement.

The backup database is further classified into two preliminary backup types

  1. Backup Database
  2. Backup Log

Each of the above types defines the type of data.

Let’s deep dive and review each of the database backup command topics to get a better understanding of what it is all about. In this article, we will find an answer for FAQs about the SQL Server database backup. We will learn more about database backup.

  1. Why are database backups so important?
  2. What is a SQL Server database backup?
  3. What are different types of database backups?
  4. What is a database recovery model?
  5. How can I check the recovery model of the database?
  6. How can I change the recovery model?
  7. What is a full database backup?
  8. How can I create a compressed backup on a newly formatted backup file?
  9. How can I overwrite to an existing backup file?
  10. How can I append backup sets in SQL Server database?
  11. How can I get Files and Filegroup information in SQL Server?
  12. What are system stored procedures that provide database information?
  13. What is a Differential backup?
  14. What are T-Log backups and How can I create T-log (Transaction-log) backups?
  15. What is a Tail-log backup?
  16. What is a Copy-only backup?
  17. What is a mirror backup?
  18. What is a Partial database backup?
  19. What is a Striped (Split) database backup in SQL Server?
  20. What is a file and filegroup backup?
  21. How can I set expiration dates for backups?
  22. How can I retain backup for a specific number of days?
  23. How can I encrypt a database backup using certificate?
  24. What is the difference between FULL, Bulk-Logged and Simple recovery models?
  25. What are system databases?

1. Why are database backups so important?

They is so important because of following reasons:

  • Data is always the target and remain vulnerable for various threats
  • Un-reliable on hardware and software programs
  • Importance of the data
  • Critical downtime
  • It all depends on the amount of time it takes to redo the work. If it’s minor; then it can be ignored. If this is a major data loss, it may take several business days and it could feasibly end up in a heavy loss for the organization.

2. What is a SQL Server database backup?

A Backup is a process to create a copy of data and it can be used to reconstruct the data in case of any failures.

Backing up your SQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, on-site, off-site, and cloud location protects and safeguard the data from potentially catastrophic or various types of data loss.

3. What are different types of database backups?

The following are the different types of SQL Server database backups.

  • Full
  • Differential
  • Transactional Log(T-Log)
  • Copy-Only
  • File
  • FileGroup
  • Partial
  • Mirror

The type of a database backup depends on the database recovery model of the database.

4. What is a database recovery model?

The Recovery Model is a property of a database that controls how the transactions are logged.

The design of entire database and recovery procedures based on the context of the recovery model of the database.

It controls and manages each transaction scope into its entirety. You can refer to it here

5. How can I check the recovery model of the database?

In the following example, query the sys.databases catalog view the recovery model of the all the databases

6. How can I change the recovery model?

The following example, the recovery model of the model database is set to FULL using the SET RECOVERY option of the ALTER DATABASE statement.

What permission is required to take a database backup?

By default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

7. What is a full database backup?

In this backup type, the whole database is backed up. This is the base for any type of backups. In order to have further differential or transaction log backups, you must create the full database backup.

How can I find the progress of percentage completion of the database backup?

Use the keyword STATS in the T-SQL to monitor backup progress status. This can also be used with restore command to measure the progress.


8. How can I create a compressed backup on a newly formatted backup file?

Use the keyword FORMAT and COMPRESSION to format and compress the database backup.


9. How can I overwrite to an existing backup file?

On specifying the INIT keyword, all the backup sets are overwritten.

10. How can I append backup sets in SQL Server database?

By default, the NOINIT option is enabled. It means that the backup will append to other backups in the file

OR

11. How can I get Files and Filegroup information in SQL Server?

You can query the sys,filegroups joined with sys. databases_files to get the filegroup related information.


12. What are system stored procedures that provide database information?

The following the system stored procedures:

  • sp_helpdb
  • sp_helpfile
  • sp_helpfilegroup

sp_helpdb ‘PowerSQL’

On specifying the database name, it displays the database information along with the list all the files and related filegroup of the database

sp_helpfile

sp_helpfile is a subset of sp_helpdb and it returns files, filegroup, and their properties.

sp_helpfile PowerSQL_1

On specifying the parameter, the file, it will list the details of that specified file and associated details.

sp_helpfilegroup

sp_helpfilegroup lists all the filegroups and number of associated data files in each filegroup.

On specifying the filegroup, the output lists the specific filegroup and associated data file details.

13. What is a Differential backup?

A Differential backup is also a type of SQL Server database backup where it copies all the data that has changed since the last full backup.

14. What are T-Log backups and How can I create T-log (Transaction-log) backups?

The transaction log records every modification made to the databases. The records are maintained in such a way that the system can be brought to a consistent state at any point of time with minimal or no data loss.

For T-Log backup, the full backup is the base. It takes complete log file data to write to a backup file.

The transaction log backup depends on the database recovery model and it’s relevant for the databases that are using the full or bulk-logged recovery models.

To execute the BACKUP LOG statement to back up the transaction log, specify the database name and backup device

How can I continue the database backup processes despite they encounter an error?

It’s basically overriding the default behavior of database backup process using CONTINUE_AFTER_ERROR. On error, the backup process will stop the process.

15. What is a Tail-log backup?

In case of failure, it is required to start the recovery process, the first and foremost important step is intended to ensure take care of tail part of the transaction before starting the restoration process is called tail-log backup.

WITH CONTINUE_AFTER_ERROR keyword, the SQL Server will override the default behavior even though it’s generating an error to complete the backup process.

16. What is a Copy-only backup?

It’s a special type of backup. It is independent of the conventional backups and it will not have an impact on the overall backup process.

In simple words, it is used to create a full database or transaction log backup without breaking the log chain

17. What is a mirror backup?

In some cases, it is required to create multiple copies of data into different files. You can create a maximum of three mirror copies of the data at a time.


18. What is a Partial database backup?

Partial database backup is one of the rarely used backup methods. All though it works with all recovery models, it is basically designed for simple database recovery model database. This provides flexibility for backing up only READ_WRITE_FILEGROUPS.

19. What is a Striped (Split) database backup in SQL Server?

This type of backup is mainly used where there is an issue with storage space.

In this type of database backup, the data will be split into parts and can be very useful during space constraints. Striped backup is a process of taking backup to different locations.

20. What is a file and filegroup backup?

Every SQL Server database must have a minimum of a data file and a log file. We can also create more than one files and it can be grouped together in filegroups for easier file management and administration purpose.

Using this method, the desired file\file group backup is possible

File Backup

Syntax: BACKUP DATABASE [DBNAME] FILE = N’FILENAME’ TO DISK = N’Path’

File Group Backup:

Syntax: BACKUP DATABASE [DBNAME] Filegroup = N’Filegroup Name’ TO DISK = N’Path’

21. How can I set expiration dates for backups?

If you want the backup to expire, use WITH EXPIREDATE clause option in the backup database T-SQL. The following example shows How can I back up with an expiration date on Jun 28, 2018:

22. How can I retain backup for a specific number of days?

If you want to retain the backup for the only specific number of days then use the WITH RETAINDAYS clause with the database backup command.

23. How can I encrypt a database backup using certificate?

  • Create master Key
  • Create certificate B
  • Backup the database

See Also,

Understanding Database Backup Encryption in SQL Server

24. What is the difference between FULL, Bulk-Logged and Simple recovery models?

FULL BULK LOGGED SIMPLE
Log level All All, but minimal log for few operations such as bulk operations(bcp, BULK INSERT) Select into, Create index, alter index, drop index, updatetext, and writetext Minimal
Log Truncation Process (LTP) Life During backup process During backup process Every time Checkpoint background run against the database
Can it use for Production Server? Yes Depends – Yes/No Depends – Yes/No
Point-in-time recovery Yes No No
Log backup support Yes

Yes

No
Piecemeal Restore Yes Yes Yes
Log Shipping Support Yes Yes No
Database Mirroring Support Yes No No
Database Replication Support Yes Yes Yes

25. What are system databases?

System databases are an essential component of SQL Server engine for the functioning of a server instance. The system databases are critical as it stores meta-data of the user-defined databases. It must be backed up after every significant update as we do it for user-defined databases. The system databases that you must always back up include msdbmaster, model and configuration databases.

System database Description Backup? Recovery model
master The database is used to record all of the system level information Yes Simple
model

It’s a template for all databases.

Yes User configurable*
msdb It is used by SQL Server Agent for job management and it also stores a history of every backup and restore operations Yes Simple (default)
Resource A read-only database that contains copies of all system objects that ship with SQL Server No
tempdb A workspace for SQL Server No Simple
Configure Distribution Configuration database for replication Yes Simple


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 Azure Data 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

Prashanth Jayaram
168 Views