SQL Server Interview Questions Part 1

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 file groups. 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 since SQL Server 2012.

1. Full Backup
2. Differential Backup
3. Transaction Log Backup
4. Copy-Only Backup
5. File or File group 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:

1.  D = Database
2.  I = Differential database
3.  L = Log
4.  F = File or filegroup
5.  G =Differential file
6.  P = Partial
7.  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:

1.  sysadmin – fixed server role
2.  db_owner –  fixed database role
3.  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 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.

32) 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.

33) 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)

34) 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.

35) 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

36) 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.

37) 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…

38) 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.

39) 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.

40) 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.

41) 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

42) What is a MASTER KEY?

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

43) 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.

44) 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

45) 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

46) 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.

47) 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.

48) 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.

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

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

50) 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.

51) 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.

52) 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

53) 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.

54) 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

55) 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.

56) 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

57) 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

58) 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.

59) 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.

60) 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.

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

RESTORE HEADERONLY

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

GO

62) 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.