Backup and Restore Your Microsoft SQL Server on AWS Cloud – Part 1

MSSQL is the most popular type of database on Windows platforms. It is a relational database management system developed by Microsoft. The primary function of this database software product is to store and retrieve data as requested by other software applications.
In this article, we take a look at MSSQL hosted on AWS and show three inherent options for backup and recovery processes that are provided by Microsoft. For this article, we have used the MSSQL 2008 R2 Standard Edition. However, the same backup and restore mechanism offered by MSSQL may be mostly applicable to other MSSQL versions.

MSSQL’s Built-In Backup and Restore Process

In an ideal world, hard drives and other hardware never fail, software is never defective, users do not make mistakes, and hackers are never successful. However, we live in a less than perfect world and we should plan and prepare to handle adverse events.
In our database environment, that plan is called the Data Recovery Plan (DRP). The DRP is the set of actions that database administrators (DBAs) take to handle adverse events that might affect the availability of their database environments.
Why should you consider performing backup and restore?
There are three main reasons for performing backup and restore operations:

1. Recover lost data: This is the most obvious objective of a backup/restore plan. In case of an event, you will be able to recover data that is lost.
2. Minimize data loss (Improve Recovery Point Objective – RPO): Your plan should also try to minimize the amount of data that is lost. For example, if you back up databases daily, your system might lose up to 24 hours of data. You will reduce potential losses if you perform additional backups during the day.
3. Minimize the impact of downtime (Reduce Recovery Time Objective – RTO ): Another factor to consider is how difficult and time consuming it would be to restore your system, and how to reduce the cost of potential downtime. Sometimes, you might want to restore a database with full backup as well as with a number of transaction logs; the cost of downtime for that database might be higher than restoring one full data backup with one differential and fewer transaction log backups.

The section below explains the full and differential backup options for MSSQL.
Backup Types
SQL Server offers three basic types of backups: Database (full), Differential, and Log backups.

1. Full Backup
   A full data backup contains all of the data in a specific database, a set of filegroups or files and all of the log files that allow you to recover whole data. A full database backup backs up the whole database together with part of the transaction log, which includes all open transactions and transactions that occurred during the backup process so that the full database can be recovered after a full database backup is restored. Full database backup represents the database at the time the backup is finished. This allows the restore process to achieve transactional integrity.
   The following is an example of the Full Database Backup command:
   BACKUP DATABASE efy
   TO DISK = N’C:\Backup\efy.bak’
2. Differential Backup
   Differential backups are designed to reduce the amount of time it takes to perform full backups. Instead of copying the whole database, the differential backup only copies the data that has changed since the last full backup. Similar to the full backup, differential backups also copy a part of the transaction log to maintain transactional integrity when the backup is restored. The following is an example of the syntax for a Differential Backup command.
   BACKUP DATABASE efy
   TO DISK = N’C:\Backup\efy.bak’
   WITH DIFFERENTIAL
3. Log Backup
   The only type of backup that does not actually copy database pages is the Log backup. The Log backup copies the transaction log of the database. After it copies the log, the backup truncates the part of the log that is not required by active transactions or transactional replication.
   The following is an example of the syntax for backing up the Log:
   BACKUP LOG efy
   TO DISK = N’C:\Backup\efy.bak’

Schedule Daily Database Backups Using a Maintenance Plan

To schedule daily database backups:

1. From the Start menu, go to the Microsoft SQL server 2008 folder.
2. From the SQL server management studio, open the SQL server Manager.
3. Under Management, right-click Maintenance Plans and select New Maintenance Plan, as shown below.
4. In the Back Up Database Task dialog box (shown below), specify the type of backup and the databases you want to back up, as well as the path to where you want to store the backup file.
   
5. Click the calendar button to schedule the task , then save the task.
   

The steps above create a schedule for automatic database backups.
In the next article, we will show how easy it is to create a backup if your database is hosted on AWS EBS.
Try N2WS Backup & Recovery (CPM) for FREE!
Read Also

• How to Copy EBS Snapshots to Amazon S3 and Save on Storage Costs
• 7 Ways to Save BIG on Your AWS Storage Bill
• 10 Tips for a Solid AWS Disaster Recovery Plan

]]>