Recovery requirements for SQL on EC2

Both backup and recovery of MS SQL on EC2 have specific storage requirements.

For general prerequisites and requirements for MS SQL on EC2, see Getting Started with Clumio for Microsoft SQL on EC2.

For information on backup storage requirements, see Backup Requirements for MSSQL on EC2.

Recovery model requirements for MS SQL databases

There are two ways to generate backups for recovering MS SQL databases on EC2 instances. The type of backup you can generate depends on the recovery model selected for your MS SQL databases set by your Database Administrator.

• Point-in-time (PITR) recovery allows you to recover the database to a specific point in time. This is not available for databases using the Simple Recovery Model.
• Full database recovery backup allows you to recover a database to the last complete backup or to a specific point-in-time, provided the backup is complete to that specific time. This requires transaction log backups and the Full Recovery Model.

Restore storage requirements

Refer to the table below for the storage requirements to restore Microsoft SQL databases in Clumio. The term "space" in the table refers to the free disk space required on the target restore location, equivalent to the disk space used by the database about to be recovered.

• You can restore a SQL Server database in the following ways:
• Full recovery of a database by overwriting the existing database.
• Full recovery of a database as a new database.
• PITR recovery of a database by overwriting the existing database to a specified point-in-time.
• PITR recovery of a database as a new database.

For example, consider a database with a size of 10GB:

• Additional 1x means that free space of 10GB is required on the disk/drive where the database is to be restored.
• Additional 2x means that free space of 20GB is required on the disk/drive where the database is to be restored.

An additional 1x disk space is needed in all cases to copy over the snapshot files from Clumio for a restore operation. This free space must be on the target location where the database is to be restored.

In the case of a PITR restore as a new database, an additional 2x disk space is needed. This is because Microsoft VSS has a condition in its pre-restore step, which checks if the required space is available for restore. At this point, VSS does not know that the existing space is actually occupied by the database snapshot files which have already been copied over. It just sees that there is not enough space and prompts for 10GB of space to recover the database, which generates the need for 2x additional free disk space for PITR as a new database.

Best practices and recommendations

When recovering as a new database, if the target host has the same file paths (DB, Log) as the source database, this causes path conflict and the restore may fail. Clumio recommends that you customize file paths where restored files have to be placed.