Home » rto rpo

rto rpo

sql server DBA 999

SQL Server Database mirroring modes and patner timeout

The Two modes of Database Mirroring in SQL Server for HA/DR Solutions are

  1. Synchronous mirroring( High Availability / HA )
  2. Asynchronous mirroring( Disaster Recovery / DR )

Synchronous mirroring :

In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database’s log buffer in memory, and then flushed to disk (or ‘hardened’) as quickly as possible. In database mirroring, as the principal server writes the principal database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance.The mirror receives the log records in mirror database’s log buffer and then hardens them to disk … Read the rest

sql server DBA 999

Why Copy-Only Backups taken in SQL Server?

A copy-only backup in SQL Server(any version like 2012 or 2008 or 2005) is taken in cases you do not want effect the sequence of conventional SQL Server backups.
Usually in using a normal Backup,the backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. copy-only backups serve this purpose.

Note: The transaction log is never truncated after a copy-only backup.

The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

Copy-only log backups (full recovery model and bulk-logged recovery model only)

Note:A copy-only full backup cannot serve as a … Read the rest

sql server DBA 999

SQL Server RPO and RTO

Recovery Point Objective(RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks, etc… for Business Continuity.

If a database is corrupted then can we restore it back to what it was 15 minutes ago? This would be an RPO of 15 minutes for Business Continuity..

Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure.

If a Database server just died. Time needed to bring it up and … Read the rest

sql server DBA 999

Different Restore options in SQL Server 2008

Different Restore options in SQL Server 2008:
1. Restore can be done for an entire database from a full database backup (a complete restore).
2. Restore can be done for a part of a database (a partial restore).
3. Restore can be done for specific files or filegroups to a database (a file restore).
4. Restore can be done to specific pages to a database (a page restore).
5. Transaction log Restore can be done onto a database (a transaction log restore).
6. We also have a option to Revert a database to the point in time captured by a database snapshot.

Restoring a full databaseThe following example restores a full database backup from the AdventureWorks2008R2.bak file.

Read the rest

sql server DBA 999

Different Backup options in SQL Server 2008

Different Backup options in SQL Server 2008:
1. Backup’s can be taken for a complete database, or one or more files or filegroups.
2. If the Databases are in Full recovery model or bulk-logged recovery model(discussed below) then backup’s can be taken for the transaction log (BACKUP LOG).

MIRROR TO Statement: Specifies a set of up to three secondary backup devices, each of which will mirror the backups devices specified in the TO clause. The MIRROR TO clause must be specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.

Backup Set Options

COPY_ONLY: Specifies that the backup is a copy-only backup, which does not affect the … Read the rest

sql server DBA 999


BACKUP LOG WITH TRUNCATE_ONLY is no longer a recognized BACKUP option. This command get rid of the contents of your SQL Server’s transaction log without really backing it up. This command will to invalidate the point in time recoverability of the database as this breaks the log chain.

This command is used by Database administrators when they end in situations where getting rid of the transaction log is only way by which he can shrink their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.

Microsoft always recommends that DBA’s must switch to simple recovery mode getting rid of the transaction logs instead of truncating logs by above command if no Point in Time recovery is required. … Read the rest