SQL Server DBA Interview Questions and Answers – Database Backups and Restore-1

This blog contains series of most commonly asked SQL Server DBA Interview Questions regarding one of the important DBA tasks- Database Backups and Restore. Since this is very wide topic, I will try to cover all the sub topics in 2-3 blogs.

1) What are database backups?

A Database backup is a copy of SQL Server data that can be used to restore and recover the data in case of any failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. There is another option to take Transaction Log backups when the database recovery model of a database is Full.

2) Types of Database backups?

We have below type of backup available in SQL Server 2012.

• Full Backup
• Differential Backup
• Transaction Log Backup
• Copy-Only Backup
• File or Filegroup Backup

3) What is Full Database backup?

A full backup is a backup of the entire database that contains all the data and log file records needed to recover the database to the point in time when backup completed. Full backup should be a part of backup strategy for all the business-critical databases.

Full database backup contains the complete set of data needed to restore and recover a database to a consistent state. It serves as a baseline for all other backups.

--Back up the AdventureWorks as full backup
BACKUP DATABASE AdventureWorks TO DISK = N'D:\AdventureWorks.bak'

4) What is Differential Backup?

Differential backup backups up only the data that has changed since the last full backup. A differential backup is not a stand-alone backup it needs a full backup to act as a baseline. For larger databases differential backups is common in order to save space and reduce the backup time.

In addition to being smaller and faster than full backup, a differential backup makes the restore process simpler. When you restore using differentials you must first restore the full backup followed by the most recent differential backup that was taken.

--Back up the AdventureWorks as differential backup
BACKUP DATABASE AdventureWorks TO DISK = N'c:\AdventureWorksDiff.bak' WITH DIFFERENTIAL

5) What is Transaction Log Backup?

Log backups can be taken only if the recovery model of the database is Full recovery or Bulk-logged recovery. Simple recovery model does not allow transaction log backup because the log file is truncated automatically upon database checkpoints.

Log backups are taken between full backups to allow point-in-time recovery with the exception of log backups containing bulk-logged records. Without Transaction log backups you can restore data only till the time when the full or differential backup was taken.

--Back up the AdventureWorks transaction log 
BACKUP LOG AdventureWorks TO DISK = N'c:\AdventureWorksLog.trn'

 6) What is File or File Group backup?

Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database. Another potential benefit of having filegroup backups is that if the disk on which a particular file resides fails and is replaced, just the file can be restored instead of the entire database.

BACKUP DATABASE AdventureWorks FILEGROUP='PRIMARY', FILEGROUP ='Secondary' 
TO DISK ='D:\AdventureWorks_FileGroup.bak'

7) What is COPY ONLY Backup?

Copy-only backups are introduced in SQL Server 2005 and are used to create a full database or transaction log backup without breaking the log chain. A copy-only full backup can’t be used as a basis for a differential backup, nor can you create a differential copy only backup.

--Back up the AdventureWorks database as copy only 
BACKUP DATABASE AdventureWorks TO DISK = N'c:\AdventureWorks.bak' WITH COPY_ONLY
--Back up the AdventureWorks transaction log as copy only
BACKUP LOG AdventureWorks TO DISK = N'c:\AdventureWorksLog.trn' WITH COPY_ONLY

8) What are Split Backups?

SQL Server have one more feature to database backups can split to multiple files. Using this way SQL Server run the multiple thread of database backups for each files and can be completed faster comparatively with less time and IO.

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\AdventureWorks_1.bak’
DISK = ‘D:\AdventureWorks_2.bak’,
DISK = ‘E:\AdventureWorks_3.bak’
GO

9) What is Mirrored backup?

Mirrored database backups can be used to create multiple copies of the database backups on different locations.

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\AdventureWorks.bak’
MIRROR TO DISK =  ‘D:\AdventureWorks_mirror.bak’
GO

10) What is Tail log backup?

A tail-log backup captures 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.

Tail log backup is taken in below ways:

If the database is online follow below syntax:
BACKUP LOG [database name] TO [backup device] WITH NORECOVERY
If the database is offline (example a corrupted database which does not start]
BACKUP LOG [database name] TO [backup device]  WITH CONTINUE_AFTER_ERROR

11) What is Native Backup Compression?

Database backup compression helps in creating a database backup in a compressed format (Supported SQL Server 2008 onwards based on the Edition).  Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup.

12) How can you enable Database compression on all the native backups?

Backup compression can be enabled at the SQL Server instance level as below.

 USE master;
 GO
 EXEC sp_configure 'show advanced option', '1';
 RECONFIGURE
 GO
 EXEC sp_configure 'backup compression default', '1';
 RECONFIGURE WITH OVERRIDE;
 GO
 EXEC sp_configure 'show advanced option', '0';
 RECONFIGURE
 GO

13) Is it possible to add password to a backup file in SQL Server 2012 version?

WITH password option is not available any more with SQL Server 2012 onwards.

14) In which recovery model, Transaction Log backups are possible?

Transaction Log backups are possible in Full and Bulk Logged recovery model.

15) What all operations are minimally logged when the database is in Bulk Logged Recovery mode?

• Bulk import operations (bcp, BULK INSERT, and INSERT… SELECT). For more information about when bulk import into a table is minimally logged.
• SELECT INTO operations.
• Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated.
• WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated.
• CREATE INDEX operations (including indexed views).
• ALTER INDEX REBUILD or DBCC DBREINDEX operations.
• DROP INDEX new heap rebuild (if applicable).

16) How do you know if your database backups are restorable?

We can use RESTORE VERIFY ONLY command to make sure that the Database backups are restorable.

17) What is the database that has the backup and restores system tables? 

msdb database contains information about the backup restore.

18) What are the backup and restore system tables?  What do each of the tables do?

Here are the backup and restore system tables and their purpose:

• backupfile – contains one row for each data file or log file backed up
• backupmediafamily – contains one row for each media family
• backupmediaset – contains one row for each backup media set
• backupset – contains one row for each backup set
• restorefile – contains one row for each restored file
• restorefilegroup – contains one row for each restored filegroup
• restorehistory – contains one row for each restore operation

19) For differential backups, how is the data determined for those backups?

DCM page contains information about the extent which are changed after the Full backup. Diff. backup process reads information about the changed extents and those extents are added in the differential backup.

20) In a situation with full, differential and transaction log backups being issued for a database, how can an out of sequence full backup be issued without interrupting the LSN’s?

Backup with COPY ONLY option can be used in such a situation.

21) How can I verify that backups are occurring on a daily basis?

We can verify the backup history of the database that backups are happening or not.

backupset table in msdb

22) What is the meaning of the values in Type column in backupset table.

This column tells us about the backup type.

Backup type. Can be:

• D = Database
• I = Differential database
• L = Log
• F = File or filegroup
• G =Differential file
• P = Partial
• Q = Differential partial

23) What are the permissions required to perform backup?

The user must be a member of either of the below roles

Backup:

• sysadmin – fixed server role
• db_owner –  fixed database role
• db_backupoperator – fixed database role

24) Is there any option to prevent All successful SQL Server backup entries from writing to the SQL Server Error Log?

Yes – We can enable the trace flag 3226.

25) Assume that we have to take a backup of the database with a backup size of 90 GB. There is no space available in a single disk drive instead there are 4 different drives where we have 25 GB free space on each drive. How can you perform the backup to three different drives?

We can take backup in split backups.

BACKUP DATABASE AdventureWorks
TO DISK = ‘D:\Backup\AdventureWorks1.bak’,
DISK = ‘E:\Backup\AdventureWorks2.bak’,
DISK = ‘F:\Backup\AdventureWorks3.bak’,
DISK = ‘G:\Backup\AdventureWorks4.bak’

26) Explain the below Backup script?

USE master
GO
BACKUP DATABASE [Test] TO
DISK = N'D:\ Backups\ test_full_native_1.bak'
WITH FORMAT, INIT,
NAME = N'test- Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

• FORMAT – This option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized (NOFORMAT will preserve it).
• INIT – By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it’s useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.
• NAME – The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.
• SKIP – Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn’t care if any backups existing in the backup set have been marked for availability to be overwritten.
• NOREWIND – This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.
• NOUNLOAD – When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.

27) What are the Backup and Restore Enhancements?

An enhancement introduced in SQL Server 2012 SP1 Cumulative Update 2 is enable backup and restore from the Windows Azure Blob storage service from SQL Server using TSQL

28) What are the limitations with Windows Azure Blob storage service?

The following are limitations specific to this release:

• The maximum backup size supported is 1 TB.
• In this implementation, you can issue backup or restore statements by using TSQL or SMO. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.

29) What are the restrictions on the Database backups operations?

Some typical examples include the following:

• You request a file backup of specific files, but one of the files is not online. The operation fails. To back up the online files, you can omit the offline file from the file list and repeat the operation.
• You request a partial backup, but a read/write filegroup is offline. Because all read/write filegroups are required for a partial backup, the operation fails.
• We request a full database backup, but one filegroup of the database is offline. Because all filegroups are implicitly included in a full database backup, this operation fails.To back up this database, you can use a file backup and specify only the filegroups that are online.

30) What all operations are prohibited when the database backups are running?

Operations that cannot run during a database backup or transaction log backup include the following:

• File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.
• Shrink database or shrink file operations. This includes auto-shrink operations.
• If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

31) What is Back up WITH CHECKSUM?

SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.

The BACKUP statement optionally computes a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page. When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.mssqltips.com/sqlservertip/1517/sql-server-dba-backup-and-recovery-interview-questions/

https://www.simple-talk.com/sql/database-administration/sql-server-backup-questions-we-were-too-shy-to-ask/

http://sql-articles.com/articles/dba/types-of-backup-in-sql-2005/

http://www.sqlteam.com/article/introduction-to-sql-server-database-backups

http://udayarumilli.com/sql-server-backup-restore-qa/