1. What are the Database recovery models available in SQL Server?

SQL server support 3 types of recovery models.

• Full.
• Simple.
• Bulk Logged.

2. What are the Different types of Backups available in SQL Server?

SQL Server supports different type of backup for database. However, which type of database backup can be performed totally depends upon the recovery model of the database one choose.

• Full Backup.
• Differential Backup.
• Transaction Log Backup.
• Tail Log Backup.
• Mirrored Backup.
• Copy-Only Backup.
• File and File group Backup.

3. What are the permissions required to perform Backup and Restore the Database?

Backup: To perform backup the login must be assigned to any of the below roles.

• SYSADMIN- Fixed server role.
• db_owner- Fixed database role.
• db_backupoperator -Fixed database role.

Restore: To perform restore the database the login must be assigned to any of the below roles.

• SYSADMIN – Fixed server role.
• Dbcreator – Fixed server role.
• db_owner – Fixed database role.

4. What are some common reason why database restores fail?

• Sufficient space not available on system Drives.
• User may not have sufficient permission to perform the restore the database.
• Service account may not have permissions on backup folder.
• Syntax Error.
• SQL Server Version problem,
• Might be wrong backup location specified.
• LSN are out of sequence so that backup cannot be restored.

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

• Using RESTORE VERIFYONLY command to check the whether SQL database backup can be read and restore.

6. Can we able to take the backup for ‘Resource Database’?

• NO, We can’t take  backup for Resource database.But we can take physical file backup for Resource database MDF and LDF files.

7. How can we rebuild Resource system database? 

• Run SQL Server installation Setup.exe file and from installation wizard from left navigation pane select ‘Maintenance’ and click on Repair. It rebuild the Resource database.

8. How can full Backup be issued without interrupting the LSN?

• Issue the Backup command with the COPY_ONLY option.

9. Can we perform a tail log backup if MDF file is corrupted?

   YES, We can perform a trail log backup as long as the LDF file if not corrupted. Actually the tail log backup is having 2 options.

1. With Norecovery.
2. Continue After Error.

WITH NORECOVERY:  To make sure no transactions happens after the tail log backup.

CONTINUE AFTER ERROR: Just tp make sure log backup happens even though some meta data page corrupted.

10. What are the restore options available in SQL Server?

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

1. With Recovery.
2. With No Recovery.
3. Standby\ Read only.

11. Is it possible to restore a database backup of SQL Server 2014 to SQL Server 2008\2012?

No it’s not possible to restore the upper version database backup to lower version SQL Server.

SQL Server installation interview Questions and Answers

Installation of SQL Server 2019