Creating Backups on SQL Server for Disaster Recovery
This tutorial explains three types of backups and demonstrates how to create full and differential backups using Microsoft SQL Server.
Join the DZone community and get the full member experience.Join For Free
Backups are one key to a successful disaster recovery plan. Every database engine has its own backup commands and procedures, and Microsoft SQL Server is no exception. SQL Server has capabilities for full and differential backups as well as a backup process for transaction logs. These procedures can be used in combination to ensure limited downtime should your database suffer from an outage or critical, unrecoverable crash.
Full Backups vs. Differential Backups
Before creating a backup, it's important to know the different types. There are three types: full, differential, and incremental. SQL Server supports full and differential backups, but some administrators incorrectly call differential backups "incremental." There is a distinct difference between the two, however, and it affects the way databases see backup data.
In addition to the different types of backups, it's also important to understand that data backups are separate from log backups. Data is the content stored in the database. Logs are files that keep a record of each transaction run against the server. Logs contain numerous data points including queries run against the server, settings changes, administrative changes such as user account updates, and security events. Administrators use audit data for forensics into data breaches, but transactions are important for disaster recovery.
You may also enjoy: Why Your Business Needs to Backup the Database
Full backups are easy to explain. These files contain an entire snapshot of all data contained in the database. Full backups include the structure of tables, views, triggers and any other object configured on the database. All backup types first require a full backup, so no matter your chosen procedure, you start with this type.
Differential backups take a snapshot of all data changed after the initial full backup. Every differential backup in SQL Server is appended to the initial full backup file. These files can grow quite large because, for each day that passes, more data changes and gets added to the database. Differential backups are cumulative from the initial full backup. Large organizations are encouraged to determine the amount of data that changes each week and take additional full backups to avoid having very large differentials.
The final type of backup in the database world is incremental. Although SQL Server doesn't support them, you might run across them in other database environments. Incremental backups take a snapshot of data changed since the last incremental. If no incremental backup exists, a snapshot of data changes from the full backup is taken. Incremental backup files are much smaller since they can be taken every few minutes for large database servers, and changes from data within those few minutes are stored.
In SQL Server environments, it's common to take a full backup at the beginning of the week, and then take a differential each day. This process depends on the size of the organization and the amount of data that changes each day. Critical databases with heavy traffic can have backups taken every 10-15 minutes, but databases with little traffic might only need nightly backups.
Creating a Full Backup of Data and Logs
SQL Server has several options for full backups. You can compress and encrypt them. You can tell SQL Server to overwrite old backups and just leave the new one on your media. You can specify the media where you want to store the backup. Administrators use network resources to store data, but this option requires a large storage capacity. An optical disk is another option that saves space on the network and avoids performance degradation during backup procedures. However, backups aren't readily available compared to files stored on network resources such as network-attached storage (NAS).
The following is an example SQL Server MSSQL backup statement:
The above statement takes a full backup of the "Store" database and stores it to disk. The name of the backup is the date. Using the date in backup names makes it easier for database administrators to identify the latest one in a serious of backup files.
checksum option provides I/O error detection. For each backup created, SQL Server creates a
checksum value and writes it to the backup header. The
checksum is recalculated when a backup is then read into memory. If the values do not match, SQL Server determines that the backup is corrupted. This backup option is a great way to avoid storing corrupted backups that could cause an unsuccessful recovery during a disaster.
Security of backups are important, because they contain all data assets. They can also take massive amounts of data storage space when they grow to several hundred gigabytes. The following MSSQL statement compresses and encrypts the backup:
To use encryption, you also need a certificate used with the backup statement. The certificate specified in the above statement is
StoreBackupCert, and the encryption used is 256-bit AES. The
compression option will compress the backup to reduce the amount of space needed. The
stats option will display a completion percentage based on this number. In the above statement, the percentage interval is
10, so SQL Server will provide a complete update for every 10% completed.
For small databases, the backup process is very quick. For larger databases, administrators should perform backups during off-peak hours, because it will take several minutes and take resources on both the database and the server. If backups are copied to a network disk, then it could cause noticeable performance degradation.
Creating a Backup for Transaction Logs
When you run the previous statements, data is backed up but not transaction logs. The database synchronizes transactions with logs every few seconds. Should an outage happen, your database could be currently running a transaction and can't finish. These transactions should be rolled back and dropped. A backup of the transaction log will bring your database to the point of recovery during an outage and rollback transactions that weren't completed to avoid data corruption.
The MSSQL statement to back up logs is similar to data. An example is shown below:
In the statement above, the
restore statement backs up logs to the same location as data backups, but the file name is given the suffix "_LOG" to distinguish it from data. This file suffix makes it easier for administrators to identify data backups from logs. The date is also used in the file to indicate when the backup was taken.
After you take a backup of your data and logs, it's important to keep them in a safe location. Most administrators automate backups and keep several weeks' worth before archiving or deleting old ones. You should always have at least two weeks of backups to avoid issues should the most current one is corrupt.
Keep data secure and in a safe location. If you take copies on an optical disk, having an off-site backup will reduce risk should the organization suffer from flood or fire. Cloud backups are also an option, and they are available should you need off-site backups. Your backups should be available yet safe from attacks and disasters to keep best practice standards for a disaster recovery plan.
Why Your Business Needs to Backup the Database
Database Backup: A Conversation With an Expert
How to Recover SQL Server Database Without Backup
Opinions expressed by DZone contributors are their own.