Database Reference
In-Depth Information
■
there are third-party tools available that can simplify the process of searching for the Lsn of the operation
in the log.
Tip
Restore with StandBy
When you finish a restore process using the
NORECOVERY
option, the database is in the
RESTORING
state and is
unavailable to users. The
STANDBY
option allows you to access the database in read-only mode.
As mentioned previously, SQL Server performs the redo stage of recovery as the final step of the restore process.
The undo stage of recovery is deferred until a restore is called with the
RECOVERY
option. The
STANDBY
option forces
SQL Server to perform the undo stage using a temporary
undo file
to store the compensation log records generated
during the undo process. The compensation log records do not become part of the database transaction log, and you
can restore additional log backups or recover the database if needed.
Listing 30-11 illustrates the use of the
RESTORE WITH STANDBY
operator. It is worth mentioning that you should
not specify
RECOVERY/NORECOVERY
options in this mode.
Listing 30-11.
Restore with STANDBY option
RESTORE LOG [MyDBCopy]
FROM DISK = N'C:\Backups\MyDB.trn'
WITH FILE = 1, STATS = 5,
STANDBY = 'C:\Backups\undo.trn'
The
STANDBY
option can be used together with point-in-time restore. This can help you avoid unnecessary
restores when you need to locate the LSN to use with the
STOPBEFOREMARK
option. Think about a situation when the
log file has multiple
DROP OBJECT
transactions, and you do not know which one dropped the table that you wish to
recover. In this case, you can perform multiple restores using both the
STOPBEFOREMARK
and
STANDBY
options, querying
the database until you find the right spot for recovery.
Alternatively, you can use the
STANDBY
option together with
STOPAT
to analyze the database state at a specific time.
Designing a Backup Strategy
Every production system has two requirements, which affect and shape Backup Strategy implementation. The first
is the
Recovery Point Objective (RPO)
, which dictates how much data loss is acceptable in the case of disaster.
The second requirement is the
Recovery Time Objective (RTO),
which
defines the acceptable downtime for the
recovery process.
RPO and RTO metrics are usually included in the Service-Level Agreements defined for the system. When
RPO and RTO are not formally documented, you can determine them by interviewing stakeholders and gathering
information about their expectations.
Non-technical stakeholders often have unrealistic expectations when defining RPO and RTO requirements.
They often assume zero data loss and system downtime. It is impossible to guarantee or achieve such goals in
real life. Moreover, very small RPO/RTO adds additional load to the server and is often impractical to implement.
It is your job to educate stakeholders and work with them to define realistic RPO and RTO based on business
requirements.
The RPO dictates the recovery model that the database should use. Table
30-1
shows possible data loss and
recovery points for the different database recovery models, assuming that backup files are available and the backup
chain is intact. Obviously, if both the data and log files are corrupted, restoring the last backup is the only option,
regardless of the recovery model.
