SQL Server Database Administrator Interview Questions and Answers

The resource database is a read-only system database that stores system objects and metadata for SQL Server. System databases are essential for the functioning of SQL Server.

• The resource database is hidden and cannot be accessed directly by users or administrators.

• It contains all the system objects that are included with SQL Server, such as system views and stored procedures.

• The resource database is used to upgrade system objects when applying SQL Server updates or service pa...read more

There are 4 types of backups available in SQL Server: Full, Differential, Transaction Log, and File/Filegroup.

• Full backup: It backs up the entire database including all data and objects.

• Differential backup: It backs up only the changes made since the last full backup.

• Transaction Log backup: It backs up the transaction log, allowing point-in-time recovery.

• File/Filegroup backup: It backs up individual files or filegroups within a database.

Question about SQL Server Database Administrator skills including joining, functions, clauses, indexing, backup plans, and SSIS packages.

• Different types of joining: inner, outer, left, right, cross

• Aggregate functions: SUM, AVG, COUNT, MAX, MIN

• HAVING clause used with GROUP BY to filter results based on aggregate function values

• WHERE clause used to filter rows based on specific conditions

• Clusters used to group related data together for faster access

• Indexing used to improve quer...read more

Recovery model in SQL Server determines how transactions are logged and how the database can be restored.

• There are three recovery models in SQL Server: Simple, Full, and Bulk-Logged.

• Simple recovery model automatically truncates the transaction log and only allows restoring to the most recent full or differential backup.

• Full recovery model logs all transactions and allows restoring to a specific point in time using transaction log backups.

• Bulk-Logged recovery model is similar ...read more

The different types of Database backups include full, differential, transaction log, and filegroup backups.

• Full backup: A complete backup of the entire database.

• Differential backup: Backup of only the changes made since the last full backup.

• Transaction log backup: Backup of the transaction log, allowing point-in-time recovery.

• Filegroup backup: Backup of specific filegroups within a database.

A backup and restore strategy is essential for ensuring data recovery in case of system failure or disaster.

• Regularly schedule full and incremental backups

• Store backups in a secure offsite location

• Test backups regularly to ensure data integrity

• Have a documented restore plan in place

• Consider using third-party backup and restore tools