SQL Server Interview Questions & Answers 2
Question 6: How is a point in time recovery performed independent of a server down situation?
Ans.
It depends on which backup types are issued. In this example let’s assume that full, differential and transaction log backups are issued.
1. Restore the most recent full backup with the NORECOVERY clause
2. Restore the most recent differential backup with the NORECOVERY clause
3. Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup
4. 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
Question 7: What are your recommendations to design a backup and recovery solution?
Ans.
1. Determine What is Needed
2. Recovery Model
3. Select Backup Types
4. Backup Schedule
5. Backup Process
6. Document
7. Backup to Disk
8. Archive to Tape
9. Backup to Different Drives
10. Offsite Storage
Question 8: What are the different types of lock modes in SQL Server?
Ans.
1. Shared
2. Update
3. Exclusive
4. Schema (modification and stability)
5. Bulk Update
6. Intent (shared, update, exclusive)
7. Key Range (shared, insert, exclusive)
Question 9: What is a deadlock?
Ans.
A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. In a deadlock situation, both transactions in the deadlock will wait forever unless the deadlock is broken by an external process.
Question 10: What is Difference between DB_Owner & SYSadmin Role?
Ans.
Db_owner is just owner of a single database.
SQL Profiler is not used by him.
Db_owner is a Database Role
Sysadmin is full system Administrator.
He can use SQL Server Profiler.
DB_owner is a server role.