5 Essential SQL Server Backup & Recovery Interview Questions: Advanced Techniques & Options
Master advanced SQL Server backup and recovery scenarios to demonstrate your DBA expertise in interviews. Below are detailed explanations, T-SQL examples, and best-practice considerations for each question.
Tail-Log Backup
Explanation: A tail-log backup captures the active portion of the transaction log that has not yet been backed up. You perform it before restoring a database to ensure no committed transactions are lost, especially when the database is damaged or taken offline.
Example:
BACKUP LOG MyDB TO DISK = 'C:\Backups\MyDB_Tail.trn'
WITH NORECOVERY,
COPY_ONLY;
When to Use: Prior to a restore when the database is inaccessible due to corruption or accidental drop.
Piecemeal Restore
Explanation: Piecemeal restore lets you restore essential filegroups first (PRIMARY and critical user data) to bring parts of the database online quickly, while secondary filegroups restore later. This reduces downtime for large databases.
Example:
-- Restore primary filegroup
RESTORE DATABASE MyDB
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\MyDB_Part1.bak'
WITH NORECOVERY;
-- Restore other filegroups
RESTORE DATABASE MyDB
FILEGROUP = 'FG_Historical'
FROM DISK = 'C:\Backups\MyDB_Part2.bak'
WITH RECOVERY;
Bulk-Logged Recovery Model
Explanation: In BULK_LOGGED mode, bulk operations (e.g., BULK INSERT, index rebuilds) are minimally logged to improve performance. While it reduces log size and speed up operations, it prevents you from performing point-in-time restores for transactions during the bulk-logged backup interval.
Considerations:
Use for high-volume data loads when point-in-time recovery is not critical.
Switch back to FULL recovery immediately after bulk operations.
BUFFERCOUNT & MAXTRANSFERSIZE
Explanation:
BUFFERCOUNT specifies the number of I/O buffers used during backup/restore.
MAXTRANSFERSIZE sets the size (in bytes) of each I/O operation.
Performance Tuning: Increasing both can improve throughput on fast storage subsystems but may increase memory usage.
Example:
BACKUP DATABASE MyDB
TO DISK = 'C:\Backups\MyDB.bak'
WITH
BUFFERCOUNT = 64,
MAXTRANSFERSIZE = 1048576; -- 1 MB
Stop-at-Markers for Point-in-Time Recovery
Explanation: You can embed MARK commands in transaction log backups to designate consistent points. During restore, you recover to a specific marker, allowing precise point-in-time recovery without relying solely on timestamps.
Example:
-- During log backup
BACKUP LOG MyDB
TO DISK = 'C:\Backups\MyDB_Log1.trn'
WITH
DESCRIPTION = 'before bulk load',
MARK = 'BEFORE_BULK';
-- Restore to marker
RESTORE LOG MyDB
FROM DISK = 'C:\Backups\MyDB_Log1.trn'
WITH
STOPATMARK = 'BEFORE_BULK',
RECOVERY;