This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are the different types of Backups?
- Data backup
- Database backup
- Full backup
- Copy-only backup
- Differential backup
- Log backup
- File backup
- Partial backup
What are the different types of Recovery Model?
- Simple: transaction log backups are not supported by the simple recovery model.
- Full: you can take all data and log full backup.
- Bulk logged: Reduces log space usage by using minimal logging for most bulk operations.
What is LSN ?
Every record in the SQL Server transaction log is uniquely identified by a Log Sequence Number (LSN).
The LSNs are sequential and in the particular ordered, new LSN is always higher than the old LSN.
What is the value of DatabasebackupLSN for the first full backup file?
A DatabaseBackupLSN is a zero and FirstLSN is same as CheckpointLSN.
What is First LSN?
The log sequence number (LSN) of the first transaction in the backup set. Blank for file backups.
What is Last LSN?
The log sequence number (LSN) of the last transaction in the backup set. Blank for file backups.
What is Full LSN?
The log sequence number (LSN) of the most recent full database backup.
How to find LSN information of the Database Backup Files?
|
1 2 3 |
SELECT last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = DB_ID(); |
One scenario, As a DBA you have already created one database maintenances plan to take database full backup, differential backup and transaction log backup. This plan is running in the background as per defined schedule.
Now your client requires one ad-hoc, full database backup so you have taken full database backup for some X reason.
In other side, your defined backup plan is also running and accidently your system crashed.
Now you have to restore your database and you are restoring your database by using full backup which is taken by database maintenance plan.
Next step is to start restoring of differential backup.
But I got an error like, your database LSN are mismatch.
What do you know about this?
Yes, LSN was broken because we have taken one ad-hoc full database backup so there are two solutions.
First, we have to use ad-hoc full database backup as a base full backup.
Second, we have to take this kind of ad-hoc full database using COPY-ONLY option.
What is Copy-Only Backup?
The Copy-Only Backup is similar to Full database backup, it takes full database backup without modifying the DatabaseBackupLSN.
It does not break the chain of differential backups.
T-SQL Script to take Copy-Only Backup.
|
1 |
BACKUP DATABASE Database_Name TO BackupDevice WITH COPY_ONLY |
What is your backup retention policy?
Never store databases and backups on the same disks because when a disk failure occurs, then you lose both backups and databases.
Store database backups on both local server and remote server. If your database size is very big the, you can use a local database backups for instant standby restore.
If you are following Agile Software Development Methodology, you should take Sprint wise database backups which help us to track and manage different functionality changes.
Why you require to restore a database?
- Application failure
- Server failure
- Disk failure
- Database failure
- Database corruption
- Research data issue
- Data corruption
What is your suggestion for the SQL Server backup plan?
Establish a full backup schedule either on a multi-day, daily, weekly or monthly basis.
Incorporate differential backups into the plan to increase the available recovery points between full backups. (e.g take after every 3 to 4 hour)
If additional points in time are needed, then consider transaction log backups as a solution to meet a final level of granularity for recovery purposes. (e.g take after every 10 min)
What is your preparation before database restore happen?
- Get the version of the destination server.
- Get the version of the source server on which the backup was created.
- Match the versions of the source and destination servers.
- Ensure exclusive access to the database.
Why database restores fail?
- Unable to gain exclusive use of the database.
- LSN’s are out of sequence so the backups cannot be restored.
- Syntax error such as with the WITH MOVE command.
- Sufficient space not available on drive.
- User may not have sufficient permissions to perform the restore.
Which database contains all types of system tables and do we perform backup and restore for that system tables.
The MSDB database is the database with the backup and restore system tables.
Yes, we can perform the backup and restore operation for the MSDB.
How a differential database backup work?
If data changes on any one of the pages in an extent, a flag is set at the extent level to indicate that the extent must be backed up.
If you have 10 differential database backups, how you restore these all differential database backups?
Not require all 10 differential database backups. First restore last database full backup and restore only last taken differential database.
What are your basic steps to perform database recovery using database backups?
- Restore the most recent full backup with the NORECOVERY clause.
- Restore the most recent differential backup with the NORECOVERY clause.
- Restore all of the subsequent transaction log backups with the NORECOVERY clause, except the last transaction log backup.
- Restore the last transaction log backup with the RECOVERY clause.
