SQL Server Interview Questions & Answers
Question 1: How does the database recovery model impact database backups?
Ans.
Database recovery model is responsible for the retention of the transaction log entries.So the setting determines if transaction log backups need to be issued on a regular or not.
SQL Server database recovery models:
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.
Question 2: True or False – The native SQL Server 2005 backups are in clear text.
Ans.
True – Prior to SQL Server 2008 database backups are not encrypted.
Question 3: How can I verify that backups are occurring on a daily basis?
Ans.
1. Review the SQL Server error log for backup related entries.
2. Query the msdb.dbo.backupset table for the backup related entries.
3. Review the file system where the backups are taken to validate they exist.
Question 4: How do you know if your database backups are restorable or not?
Ans.
1. Issue the RESTORE VERIFYONLY command to validate the backup.
2. Restore the backups as a portion of a log shipping solution.
3. 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.
Question 5: Common reasons why database restores fail?
Ans.
1. Unable to gain exclusive use of the database.
2. LSN’s are out of sequence so the backups cannot be restored.
3. Syntax error.