SQL Server Backup and Restore Interview Questions

Here are the list of SQL Server Backup and Restore Interview Questions which are asked in SQL Server DBA interviews.

1. How does the database recovery model impact database backups?

Ans:

First the database recovery model is responsible for the retention of the transaction log entries.  So based on the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.

• 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.

 2. Is the native SQL Server 2005 backups are in clear text or in encrypted?

Ans:

With SQL Server 2008 is the introduction of natively encrypted database backups. Prior to SQL Server 2008 a third party product was necessary to encrypt the database backups.

 3. How can I verify that backups are occurring on a daily basis?

Ans:

• Check all backup jobs history

• Review the SQL Server error log for backup related entries.

• Query the msdb.dbo.backupset table for the backup related entries.

• Review the file system where the backups are issued to validate they exist.

 4. How do you know if your database backups are restorable?

Ans:

• Issue the RESTORE VERIFYONLY command to validate the backup. For validating LiteSpeed backups use XP_restore_verifyonly

• Randomly retrieve tapes from off site and work through the restore process with your team to validate the database is restored in a successful manner.

5. What are some common reasons why database restores fail?

Ans:

• Sufficient space not available on drive

• User may not have sufficient permissions to perform the restore

• Unable to gain exclusive use of the database.

• LSN’s are out of sequence so the backups cannot be restored.

• Syntax error such as with the WITH MOVE command.

• Version problem

• Might be wrong backup location specified

• Service account may not have permissions on backup folder

 6. What are the permissions required to perform backup and Restore?

Ans:

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

Restore:

• Sysadmin – fixed server role

• Dbcreator – fixed server role

• db_owner – fixed database role

 7. How can you be notified if a native SQL Server database backup or restore fails via the native tools?

Ans:

• Setup SQL Server Alerts to be sent to Operators on a failure condition.

• Include RAISERROR or TRY\CATCH logic in your backup or restore code to alert on the failure.

 8. Does all successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single trace flag?

Ans:

Yes – Just enable the trace flag 3226.

 9. What are some common post restore processes?

Ans:

• Sync the logins and users

• Validate the data is accurate by running dbcc commands

• Notify the team\user community

• Cleanse the data to remove sensitive data i.e. SSN’s, credit card information, customer names, personal information, etc.

• Change database properties i.e. recovery model, read-only, etc.

10. Explain how you could automate the backup and restore process?

Ans:

• Backups can be automated by using a cursor to loop through each of the databases and backup each one

• Restores can also be automated by looping over the files, reading from the system tables (backup or log shipping) or reading from a table as a portion of a custom solution

 11. What is the database that has the backup and restores system tables?  What are the backup and restore system tables?  What do each of the tables do?

Ans:

 The MSDB database is the database with the backup and restores system tables. 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

 12. How can full backups be issued without interrupting the LSN’s?

Ans:

Issue the BACKUP command with the COPY_ONLY option

 13. How is a point in time recovery performed?

Ans:

It depends on which backup types are issued.  In this example let’s assume that full, differential and transaction log backups are issued.

• Restore the most recent full backup with the NORECOVERY clause

• Restore the most recent differential backup with the NORECOVERY clause

• Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup

• Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied

 14. What are your recommendations to design a backup and recovery solution? Simply what is Backup Check list?

Ans:

• Determine What is Needed

• Recovery Model

• Select Backup Types

• Backup Schedule

• Backup Process

• Document

• Backup to Disk

• Archive to Tape

• Backup to Different Drives

• Secure Backup Files

• Encrypt or Password Protect Backup Files

• Compress Backup Files

• How Much to Keep on Disk

• Online Backups

• Run Restore Verify only

• Offsite Storage

 15. Consider a scenario where you issue a full backup.  Then issue some transaction log backups, next a differential backup, followed by more transaction log backups, then another differential and finally some transaction log backups.  If the SQL Server crashes and if all the differential backups are bad, when is the latest point in time you can successfully restore the database?  Can you recover the database to the current point in time without using any of the differential backups?

Ans:

You can recover to the current point in time, as long as you have all the transaction log backups available and they are all valid. Differential backups do not affect the transaction log backup chain.

 16. What are the three basic phases for database recovery and in what order do they occur?

Ans:

• Analysis

• Redo – rolls forward committed transactions

• Undo – rolls back any incomplete transactions

 17. What options/arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated?

Ans:

• SQL Server 2000: NO_TRUNCATE

• SQL Server 2005/2008: NO_TRUNCATE, COPY_ONLY

18. What are all of the backup \Restore options and their associated value?

Ans:

Backup Options:

• Full – Online operation to backup all objects and data in a single database

• Differential – Backup all extents with data changes since the last full backup

• Transaction log – Backup all transaction in the database transaction log since the last transaction log backup

• File – Backup of a single file to be included with the backup when a full backup is not possible due to the overall database size

• File group – Backup of a single file group to be included with the backup when a full backup is not possible due to the overall database size

• Cold backup – Offline file system backup of the databases

• Partial Backup – When we want to perform read-write filegroups and want to exclude read-only filegroups from backup. It will be useful for huge databases (Data warehousing)

• Third party tools – A variety of third party tools are available to perform the operations above in addition to enterprise management, advanced features, etc.

Restore Options:

• Restore an entire database from a full database backup (a complete restore).

• Restore part of a database (a partial restore).

• Restore specific files or filegroups to a database (a file restore).

• Restore specific pages to a database (a page restore).

• Restore a transaction log onto a database (a transaction log restore).

• Revert a database to the point in time

 19. How much time taken to take full backup of 500 GB database by using third party tool litesped and without using third-party tool and also how much time taken to restore same full backup using litespeed and without third-party tool

Ans:

There is no specific time we can say for BACKUP & RESTORE operation.

It depends on lot of factors like Disk I/O, Network, processors etc.

 SQL Server 2005:

Database Size: 1.2 TB

Time taken to Backup with Litespeed :  3:20 Hrs (80 % of compression)

Time Taken to Restore: 6Hrs

 Database Size: 800 GB

Time Taken to Backup using Native Method:  11 Hrs

I never tried restoring huge db’s in native method mean native backups

SQL Server 2000:

A Database of 20 GB will take 14 Min to Backup and 22 Min to Restore the Backup

 20. What are the issues you faced in backup and restore process?
Ans:

Common Errors in Backup:

Error 3201 – when performing a backup to a network share

Sol:

Where SQL Server disk access is concerned, everything depends on the rights of the SQL Server service startup account. If you are unable to back up to a network share, check that the service startup account has write rights to that share.

 Error: Cannot open the backup device:

Sol: 

Either the specified location is missing or the service account under which the SQL Agent is running does not have the permissions on that folder.

 Common Errors in Restore:

Error 3205 – Too many backup devices specified for backup or restore;

Sol:

The most common cause for this error is because you are trying to restore a SQL Server 2005 backup on a SQL Server 2000 instance

 Error 4305 – an earlier transaction log backup is required

Sol:

There are one or more transaction log backups that need to be restored before the current transaction log backup. Using LSN number we can identify the prior log backups.

21. How to perform the tail log backup?

Ans:

As normal log backup we can perform the tail log backup. We have two options to consider

WITH NORECOVERY:  When database online and you are planning to perform a restore after the tail log backup. It takes the database in restoring mode to make sure that no transactions performed after the tail log.

WITH CONTINUE_AFTER_ERROR: When database offline and does not starts. Remember we can only perform the log backup on damaged database when the log files are not damaged

22. What is the difference between NO_LOG and TRUNCATE_ONLY?

Ans:

Both removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. NO_LOG and TRUNCATE_ONLY are synonyms.
After truncating the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE to take a full or full differential backup. Always try to avoid running the truncating as it breaks the log chain, Until the next full or full differential backup, the database is not protected from media failure.

 23. Consider a situation where I have to take a backup of one database of 60 GB. My hard drive lacked sufficient space at that moment. I don’t find 64GB free on any drive. Fortunately, I have 3 different drives where I can hold 20 GB on each drive. How can you perform the backup to three different drives? How can you restore those files? Is this really possible?

Ans:

Yes it is possible. We can split the backup files into different places and the same can be restored.

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

• RESTORE DATABASE [AdventureWorks]
  FROM DISK = N’D:\Backup\MultiFile\AdventureWorks1.bak’,
  DISK = N’E:\Backup\MultiFile\AdventureWorks2.bak’,
  DISK = N’F:\Backup\MultiFile\AdventureWorks3.bak’

24. What is piecemeal Restore?

Ans:

Consider we have a database of 3 TB where as on primary file group is a read write filegroup of size 500 GB and we have other files groups which are read-only of size  2.5 TB. We actually need not perform backup for read-only file groups, here we can perform partial backups.

Piecemeal restore process allows us to restore the primary filegroup first and the database can be online and the remaining filegroups can be restored while the recovery the transactions are running on primary

File group. Mostly suitable for data warehousing databases.

 25 Have you ever perform the backup using T-SQL? Can you explain about different backup options?

Ans:

BACKUP [DATABASE/LOG] <File/FileGroup>

TO <Backup Device>

MIRROR TO <Backup performed to different locations>

MIRROR TO <>

MIRROR TO < Only 3 mirrors can be specified >

WITH <Options>

Below are the General WITH options

• Backup Set Options

• COPY_ONLY  – Full backup on full recovery mode db’s. No chain breaking

• COMPRESSION | NO_COMPRESSION – DB compression

• DESCRIPTION

• NAME

• PASSWORD – Can assign a pwd, same password required to restore it

• EXPIREDATE – Expires after the given date

• RETAINDAYS – number of days that must elapse before this backup media set can be overwritten

• Media Set Options

• NOINIT | INIT – Overwrite | Append

• NOSKIP | SKIP – Check Backupset expiration before overwritten | No checks

• NOFORMAT | FORMAT –

• MEDIADESCRIPTION

• MEDIANAME

• MEDIAPASSWORD

• BLOCKSIZE

• Data Transfer Options

• BUFFERCOUNT

• MAXTRANSFERSIZE

• Error Management Options

• NO_CHECKSUM | CHECKSUM

• STOP_ON_ERROR | CONTINUE_AFTER_ERROR – Instructs BACKUP to fail if a page checksum does not verify | Continue after error

• Compatibility Options

• RESTART

• Monitoring Options

• STATS  – Shows Percentage completed

• Tape Options

• REWIND | NOREWIND

• UNLOAD | NOUNLOAD

• Log-specific Options

• NORECOVERY – Performs tal log and leave db in restoring mode

• STANDBY – Performs a backup and leave db in read only mode

• NO_TRUNCATE – Specifies that the log not be truncated and causes the Database Engine to attempt the backup regardless of the state of the database

26 Have you ever perform the restore using T-SQL? Can you explain about different restore options?

Ans:

RESTORE [DATABASE/LOG] <File/FileGroup>

FROM  <Backup Device>

WITH <Options>

Below are the general WITH options

• MOVE ‘logical_file_name_in_backup’ TO ‘operating_system_file_name’

• REPLACE

• RESTART

• RESTRICTED_USER

• Backup Set Options

• FILE

• PASSWORD

• Media Set Options

• MEDIANAME

• MEDIAPASSWORD

• BLOCKSIZE

• Data Transfer Options

• BUFFERCOUNT

• MAXTRANSFERSIZE

• Error Management Options

• CHECKSUM | NO_CHECKSUM

• STOP_ON_ERROR | CONTINUE_AFTER_ERROR

• Monitoring Options

• STATS [ = percentage ]

• Tape Options

• REWIND | NOREWIND }

• UNLOAD | NOUNLOAD }

• Replication_WITH_option

• KEEP_REPLICATION

• Point_in_time_options

• STOPAT = { ‘datetime’| @datetime_var }

• STOPATMARK = { ‘lsn:lsn_number’ }

• STOPBEFOREMARK

 27. Can you restore master database? If yes how?

Ans:

All server level information stored in master database that includes logins information etc. Schedule a regular backup for master database and below is the process to restore a master db.

• Start the sql server in single user mode (-m)

• Issue the restore command with replace from SQLCMD prompt

• RESTORE DATABASE master FROM <backup_device> WITH REPLACE

• Restart the sql server in normal mode

• All databases as available at the time of master db backup must be attached as everything is tracked in master database.

• If any databases are missing we can manually attach the mdf-ldfs.

 28. How can we rebuild the system databases?

Ans:

We usually rebuild the system databases when they are corrupted.

Rebuild deletes the databases and recreates it hence all the existing information is vanished.

Before rebuild:

• Locate all recent backup of system databases

• Make a note on mdf and ldf file locations, server configuration, Build /hotfix /sp applied

Rebuild:

• Locate the Sql Server installation bits and run the command setup.exe fro command prompt by passing the argument as “/ACTION=REBUILDDATABASE”

• Review the summary.txt once the rebuild completes

Post Rebuild:

• Restore all the system databases from existing backups

• Move the system databases mdf/ldf files to the actual locations

 29. How can we rebuild Resource system database?

Ans:

In Sql Server 2008 from installation wizard from left navigation pane select “Maintenance” and Click on Repair. It rebuilds the resource database.

 30. As a database is recovering, after which phase will the database be available/online?

Ans

SQL Server 2000: After the Undo phase.

SQL Server 2005/2008:  In all editions but Enterprise, after the Undo phase (if running FULL recovery model). With Enterprise edition, after the Redo phase. Fast recovery is possible because transactions that were uncommitted when a crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users.

On the Enterprise Edition of SQL Server 2005/2008, users are allowed access after REDO. So the point is REDO phase is done first.

31. How do you respond to the increasing transaction log file?

Ans:

Alternatives for responding to a full transaction log include:

• Backing up the log.

• Freeing disk space so that the log can automatically grow.

• Adding a log file on a different disk.

• Completing or killing a long-running transaction.

• Moving the log file to a disk drive with sufficient space.

• Increasing the size of a log file.

• Shrinking the log file.

 32. How to rebuild the system databases in SQL 2008?

Ans:

• First we need to confirm that the master database is corrupted. We cannot restart SQL Server without the MASTER database. By checking the error logs  we can confirm that master database is corrupted.

• To rebuild the master database we have to use setup.exe from command prompt. There is no much difference between 2005 and 2008 except few command line switches.

• Find the setup.exe file (C:\……………………….\100\Setup BootStrap\Release\setup.exe)

• Run the below command from dos prompt

c:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release>setup.exe

/QUIET

/ACTION=REBUILDDATABASE

/INSTANCENAME=<Default / Named>

/SQLSYSADMINACCOUNTS= <Service Account>

[/SAPWD=<Mandatory when using Mixedmode>]

[/SQLCOLLATION=<Give new collation if you want to change default>]

• When setup has completed rebuilding the system databases, it will return to the command prompt with no messages (It always first prints out the version). Examine the “Summary” log file (100\setup bootstrap\logs) to verify it was completely successful.

 33. Do we need installation DVD or complete binaries to rebuild system databases?

Ans:

The answer is NO. In 2008 this is really a nice enhancement. While installing SQL Server these system database files are copied to the location (<MSSQL.InstanceName>\MSSQL\Binn\Templates)

In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup. Hence when we rebuild the databases the setup uses these files to rebuild the databases.

34. What should we do if cannot find these database files at ….Templates\ location?

Ans:

There are two options available.

• Use the Repair feature of Setup (Available from the Maintenance option of the SQL Server Installation Center installed your machine)

OR

• Manually copy the necessary file(s) yourself. On your media source find the directory of your platform (x86, x64, or ia64). Then go to the following directory:

setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn\Template

 Once you have copied the file into the templates directory or repairing, re-run setup with the syntax I’ve described above.

 35. Can we rebuild resource database?

Ans:

Yes! To rebuild these database files you would need to run Repair from the Installation Center.

 36. Can we rebuild msdb?

Ans:

Yes! We can directly restore it from a valid backup. If there is no valid backup available, restore all system databases as described above.

 37. What if I have applied Updates / HotFixes after installation?

Ans:

As with SQL Server 2005, if for any reason you rebuild system databases or repair the resource database, you should apply your latest update even if you restore backups of system databases.

 38. How to restore Master or Msdb database from a valid backup?

Ans:

• Stop and Start the SQL Server Database Engine in Singe User Mode (Using parameter –m)

• Restore the Master Database from SQLCMD prompt

• From dos prompt using SQLCMD connect to the sql server and run the restore script

• RESTORE DATABASE MASTER FROM DISK=’D:\MASTER_FULL.BAK’ WITH REPLACE

• Stop and start the SQL Server Database Engine in normal mode

• Restore MSDB Database

• Connect to management studio and run the restore script for msdb

• RESTORE DATABASE MSDB FROM DISK=’D:\MSDB_FULL.BAK’ WITH REPLACE

39. What is the difference between Hot and Cold Backup?

Ans:

Performing backup while the database is online is called Hot backup. Stopping SQL server service and copying MDF and LDF files is called cold backup which is not really happens in production.

 40. What are the restore options available?

Ans:

When you restore a backup, you can choose from 3 restore option.

1. With Recovery – Database is ready to use, and user can connect to database, user can change data inside database.

2. No Recovery – Database is not ready, there are few more backups that has to be applied to this database instance. User cannot connect to database because it is in Restoring Status. (Exception: Not considering Database Snapshots )

3. Standby / Read Only – Database is ready to use but database is in Read Only mode, user can connect to database but they cannot change data inside database. A running database con not be changed to standby mode. Only a data in no-recovery state can be moved to standby mode. This is an option that is specified while restoring a database or transaction log.