SQL Server Interview Questions for DBAs

Practice Backup and Restore

Create a full backup of a sample database and restore it to a new database instance.

Expected output: A restored database identical to the original.

Hint: Use BACKUP DATABASE and RESTORE DATABASE commands with appropriate file paths.

Analyze Execution Plans

Run a query and analyze its execution plan to identify potential performance improvements.

Expected output: A report identifying costly operations and suggestions for indexing.

Hint: Use SQL Server Management Studio's 'Display Estimated Execution Plan' feature.

SQL Server supports full, differential, and transaction log backups. Full backups capture the entire database, differential backups capture changes since the last full backup, and transaction log backups record all transactions for point-in-time recovery.

Performance can be monitored using tools like SQL Server Profiler, Extended Events, Dynamic Management Views (DMVs), and Performance Monitor. Key metrics include CPU usage, disk I/O, wait statistics, and query execution plans.

A clustered index determines the physical order of data in a table and there can be only one per table. A non-clustered index is a separate structure that points to the data rows and multiple non-clustered indexes can exist per table.

TDE encrypts the data and log files at rest to protect sensitive data from unauthorized access, without requiring changes to the application.

DBCC CHECKDB is used to check the logical and physical integrity of all database objects. It helps detect corruption and inconsistencies.