SQL Server DBA Backup Interview Questions focuses on one of the most common tasks that a DBA does on a day to week schedule. We are covering one of the most common interview questions asked related to Database Backups.
What are the different types of backup’s supported by SQL Server?
SQL Server supports two types of backup device & locations:
- Backup to File (Save to disk either Locally or on a Network Drive)
- Backup to URL (To Azure Blob Storage on the Cloud)
Irrespective of the backup location, you can backup the SQL Databases using the following Backup Types:
- Full Backup
A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
- Differential Backup
A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data extents that have changed since the differential base.
A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.
- Transaction Log Backup
A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)
- Copy-only backup
A special-use backup that is independent of the regular sequence of SQL Server backups.
- Partial backup
Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.
What are some of the Restrictions on Backup Operations?
- Filegroups that are Offline cannot be backed up. If the Offline Filegroup is included in the backup operation, then the backup job will fail. This is applicable for all types of backups like Full, Partial backups etc.
- When a database file is being created or deleted, the backup operation is suspended and will wait until the Database creation process is complete.
- You cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE option when a backup process is running.
- Shrink database, shrink file or auto-shrink operations cannot run during backup operations.
- When a Database backup is running, Delete or Create Database statements will fail.
How to do a Point in Time restore of a SQL Server Database?
You can only do a Point in Time restores when your database is running Bulk-Logged or Full recovery models. Simple recovery model is not supported for doing Point in Time restores.
Using a A tail-log backup you can capture any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.