Database Reference
In-Depth Information
The database recovery model affects the possibilities for disaster recovery of a database.
1. A
SIMPLE
recovery model will not allow transaction log backups, and all the changes
since the most recent backup are unprotected. The point-in-time recovery is only
possible to the end of a backup.
2. The required backup strategy for a
SIMPLE
recovery model must be
FULL
and
DIFFERENTIAL
backups, which contain just enough log data to recover the database.
3. The restore sequence starts with restoring a full database backup, followed by a
corresponding differential backup.
4. A
FULL
recovery model requires a regular interval of transaction log backup, which
can recover to an arbitrary point in time and all committed transactions can be
recovered in the event of disaster.
5.
Back up the tail of the log to capture the log records that have not yet been backed
up. The tail-log backup is taken by using the
BACKUP
LOG
database_name
TO
<backup_device>
WITH
NORECOVERY
statement.
6.
The typical restore sequence will be restoring to a point-in-time, recovering to a
marked transaction and recovering to a log sequence number.
7.
To perform point-in-time restores, an operation that involves full database backup
and multiple log backups (performed every hour every day) are essential, such as
restore a database to its state as of
15:00
hours on
December
31,
2010
.
The steps are:
--Obtain list of backup file numbers
RESTORE HEADERONLY FROM AdventureWorks2008R2Backups
GO
--RESTORE from FULL database backup
RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=5, NORECOVERY;
--RESTORE from transaction log backup files
RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=14, NORECOVERY, STOPAT = 'Dec 31, 2012 14:00 PM';
RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=15, NORECOVERY, STOPAT = 'Apr 15, 2012 15:00 PM';
RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY;
GO
Search WWH ::
Custom Search