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.
SQL Server supports two types of backup device & locations:
Irrespective of the backup location, you can backup the SQL Databases using the following Backup Types:
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.
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.
A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)
A special-use backup that is independent of the regular sequence of SQL Server backups.
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.
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.
Comments are closed.