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

Continuing my Blog series on the SQL Server DBA Interview Questions on Database Backup Restores, I am posting another set of 31 questions which will help you to understand the SQL Server Database backups topic in more depth.

1) What are the Best Practices recommendations related to SQL Server Database backups?

Backup is an important component of a sound disaster recovery strategy. Here are some best practices you can follow to ensure you have a good backup in place:

• Make sure you are not storing your backups in the same physical location as the database files. When your physical drive goes bad, you should be able to use the other drive or remote location that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. Carefully study the disk partition and logical column layouts before choosing a storage location for the backups.
• Make sure you have a proper backup schedule established according to the needs of the application and business requirements. As the backups get old, the risk of data loss is higher unless you have a way to regenerate all the data till the point of failure.
• Make sure to actually restore the backups on a test server and verify that you can restore with all the options and conditions you need to use during a planned or un-planned downtime.
• Use the verification options provided by the backup utilities [BACKUP TSQL command, SQL Server Maintenance Plans, your backup software or solution, etc].
• Use advanced features like BACKUP CHECKSUM to detect problems with the backup media itself.

2) Can we have multiple copies of the database backup is a single file?

Yes we can save multiple copies of database backup in a single file.

3) Name any 2-3 Third party SQL Server database backup tools?

There are many tools available in the market for SQL server backups like

• SQL Litespeed (Dell)
• SQL Backup Pro (Redgate)
• SQL Safe Backup (Idera)

4) How many copies are allowed when taking a backup using MIRROR Backup option?

Three copies are allowed in a Mirror backup apart from the original copy.

5) What are the common issues you faced in Database backup?

There could be multiple reasons like:

• Permissions issues if the backups are configured to be taken on a share location
• Backup file used by the tape backups due to which backup process is not able to overwrite the backup file.
• Full backup is not taken before initiating a Diff. of Transaction log backup
• Not enough space available on the target location

6) What is RTO?

Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure.

7) What is RPO?

Recovery Point Objective (RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks, etc…

8) What is TDE (Transparent Data Encryption) method in SQL Server?

TDE provides the ability to encrypt an entire database and to have the encryption be completely transparent to the applications that access the database. TDE encrypts the data stored in both the database’s data file (.mdf) and log file (.ldf) using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption. In addition, any backups of the database are encrypted. This protects the data while it’s at rest as well as provides protection against losing sensitive information if the backup media were lost or stolen.

9) Which versions of SQL Server support TDE?

TDE requires SQL Server 2012 Enterprise edition. It’s not available in SQL Server 2012 Standard or Business Intelligence editions. TDE is also available in SQL Server 2008 and SQL Server 2008 R2 Datacenter and Enterprise editions.

10) Is there a performance impact for using TDE?

Yes, some performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload.

SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.

11) How can you enable TDE in SQL server?

TDE can be enabled on the database using below steps:

1. Create a master key for the database.
2. Create a certificate that’s protected by the master key.
3. Create a special key that’s used to protect the database. This key is called the database encryption key (DEK) and you secure it using the certificate.
4. Enable encryption.

-- The master key must be in the master database.
 USE master;
 GO
  -- Create the master key.
 CREATE MASTER KEY ENCRYPTION
 BY PASSWORD='YourPassword';
 GO
  -- Create a certificate.
 CREATE CERTIFICATE MySQLCert
 WITH SUBJECT='MyDatabase DEK';
 GO
  -- Use the database to enable TDE.
 USE MyDatabase
 GO
 -- Associate the certificate to MyDatabase.
 CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE MySQLCert;
 GO
  -- Encrypt the database.
 ALTER DATABASE MyDatabase
 SET ENCRYPTION ON;
 GO

12) What is a MASTER KEY?

A master key is a symmetric key that is used to create certificates and asymmetric keys.

13) What is the below error?

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint..
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

This issue occurs when somebody try to restore the database backup of TDE database on a different SQL Server instance.

14) What are the Advantages of using TDE?

• Performs real-time I/O encryption and decryption of the data and log files
• Encrypts the Entire Database in rest
• No architectural changes needed
• No application code changes are required and the user experience is the same
• Easy to implement
• DBAs can still see the data

15) What are the Disadvantages of using TDE?

• Not granular – Cannot just encrypt specific tables/columns
• Not good for high CPU bottleneck servers
• Not protected through communication/networks

16) What is MAXTRANSFERSIZE option in Backup database command?

MAXTRANSFERSIZE : specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 64 KB ranging up to 4194304 bytes (4 MB).  The default is 1 MB.

17) What is BUFFERCOUNT option in Backup database command?

BUFFERCOUNT specifies the total number of I/O buffers to be used for the backup operation.   The total space that will be used by the buffers is determined by: buffercount * maxtransfersize.

18) What is a log chain?

A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery.

Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups.

19) Whether Full or Differential backups clear the Transaction Log or not?

No, Full or Differential backup do not clear Transaction logs.

20) Is it possible in any situation when differential backup grows more than the Full backup?

Yes, it is possible in case when you do not take Full backup of the database for months and change in the databases grow more than the size of the Full backup.

21) Is it mandatory to take a Full backup if we switch the recovery model of a database?

Yes, It is mandatory to take a Full backup of the database after switching the recovery model of the database to initiate the log chain. Otherwise Diff. or Transaction logs will fail.

22) What are the options to deal with Over Growing transaction log file?

We have below options to deal with the over growing transaction log file:

Freeing disk space so that the log can automatically grow.

• Backing up the log.
• Adding a log file on a separate disk drive.
• Increasing the size of a log file
• killing a long-running transaction

23) How does the database recovery model impact database backups?

Database recovery model deals with the retention of the transaction log entries. Database recovery model decides if transaction log backups need to be triggered on a regular basis in order to keep the transaction log small or the Transaction logs will be truncated automatically.

•  Simple – Committed transactions are removed from the log when the check point process occurs.
• Bulk Logged – Committed transactions are only removed when the transaction log backup process occurs.
• Full – Committed transactions are only removed when the transaction log backup process occurs.

24) What is Windows Azure Blob storage service Database backups?

SQL Server 2012 SP1 CU2, enables SQL Server backup and restore directly to the Windows Azure Blob service. Backup to cloud offers benefits such as availability, limitless geo-replicated off-site storage, and ease of migration of data to and from the cloud. In this release, you can issue BACKUP or RESTORE statements by using tsql or SMO. Back up to or restore from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore Wizard is not available in this release

25) What is a SQL Server Credential?

A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. Here, SQL Server backup and restore processes use credential to authenticate to the Windows Azure Blob storage service. The Credential stores the name of the storage account and the storage account access key values. Once the credential is created, it must be specified in the WITH CREDENTIAL option when issuing the BACKUP/RESTORE statements.

26) What is SQL command to create SQL Server Credential?

CREATE CREDENTIAL mycredential
WITH IDENTITY= 'mystorageaccount' 
--this is the name of the storage account you specified when creating a storage account, SECRET = '<storage account access key>' 
-- this should be either the Primary or Secondary Access Key for the storage account to access cloud --account

27) What is the command to place the database backup on a Windows Azure Blob storage service?

BACKUP DATABASE AdventureWorks2012
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_1.bak'
URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_2.bak'
WITH CREDENTIAL = 'mycredential' ,
STATS = 5
GO

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

• Flexible, reliable, and limitless off-site storage: Storing your backups on Windows Azure Blob service can be a convenient, flexible, and easy to access off-site option.
• No overhead of hardware management
• Cost Benefits: Pay only for the service that is used. Can be cost-effective as an off-site and backup archive option.

29) Suppose I have a Database maintenance plan which runs every 15 minutes to take the Transaction Logs backup of all user defined databases. One of the members of DBA team created a new database in the morning at 09:10 AM and the DB maintenance job started failing. What could be the reason?

This job is failing because the we did not take a full database backup of the newly created database. We need to a full backup of a database to initiate the log chain.

30) What is the below error related to Differential backup?

Msg 3035, Level 16, State 1, Line 1Cannot perform a differential backup for database "backup_test", because a current database backup does not exist.

Differential Backup is failing because we did not take a full backup of the database after creation of the database or switching the Recovery model of the database.

31) How will check the content of a backup file?

RESTORE HEADERONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'
GO

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://sqlmag.com/database-security/using-transparent-data-encryption

http://sqlmag.com/sql-server/transparent-data-encryption-faqs

http://sqlserverzest.com/2013/10/03/sql-server-restoring-a-tde-encrypted-database-to-a-different-server/

http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

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

https://msdn.microsoft.com/en-us/library/jj919148(v=sql.110).aspx#intorkeyconcepts