Back up and Restore Flashcards
What are the 2 main recovery models?
Simple and Full
What is the work loss exposure using simple recovery model?
Changes since the most recent backup are unprotected.
What is the work loss exposure using full recovery model?
Normally none.
What 4 database features are not available under simple recovery model?
- Log shipping
- Always On or Database mirroring
- Media recovery without data loss
- Point-in-time restores
What is a differential backup?
Backup any changes since the last complete backup
What 5 types of backups can you create under the simple recovery model
- Complete backups
- Differential backups
- File and/or Filegroup backups
- Partial backups
- Copy-Only backups
In order to restore to a point in time what back ups would need to be made?
Full or differential backups together with transaction log back ups
In 5 steps how would you recover following a failure in a database which uses full recovery model with differential and transaction log backups?
1) Backup active transaction log (Tail of dog)
2) Restore last full backup with no recovery
3) Restore most recent partial backup with no recovery
4) Each transaction log in order since last partial backup with no recovery
5) The tail of do with recovery
Give 4 steps to recover a mission-critical database system for which a full database backup is created daily at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. To restore the database to the state is was in at 5:19 A.M. Wednesday
1) Restore full backup Tuesday midnight
2) Restore partial created at 05:00
3) Restore 05:10 log
4) Restore 05:20 log with transactions before 05:19
What is a partial backup
It contains all the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.
What is log shipping
The transaction log files are backed up to a location and used to restore secondary servers
What state must the destination database be in for log shipping to be used
No recovery mode or Standby mode
How does SQL server mirroring differ to log shipping?
- Mirroring: Works by redoing all inserts, deletes and updates (active transactions) on the primary, as quickly as possible
- Log shipping: Transaction log files are backed up to a location and used to restore secondary servers
Which option of log shipping or mirroring is best used for high availability?
mirroring
Which option of log shipping or mirroring is best used for disaster recovery?
log shipping
