Understanding the Importance of Database Backups for SQL Server

By Edward McGovern, Raffa Technology

If you are working with accounting systems for the mid-market – products like Dynamics SL or Dynamics GP – then you are most likely using SQL Server to store your accounting data. Naturally, you understand how important this data is to your organization or you would not have made the investment to capture the accounting data. In order to protect your investment, you must do a periodic backup of data that needs to be verified and monitored. Your data is your property. No consultant owns it. No developer who works on your systems owns it. You own it and you are responsible for it.

how-to-backup-database-on-sql-serverThe responsibility is significant. Fortunately, there are many tools you can use to develop and implement a disaster recovery plan (DRP) for your SQL data. And, yes, you do need a plan. You need to consider down-time tolerances and other factors. The easiest DRP to implement is using the tools already built into SQL Server. Setting up SQL Server to periodically backup databases and perform a tape or online backup of those backups is very simple. However, you need to understand how Recovery Model settings in SQL Server influence the behavior of the system and what you need to do to manage those options. Recovery Models need to be set properly even if you are using online software to perform live backups of SQL Server.

First, you must manage the type of model you choose. There are two basic recovery models in SQL Server:

  1. Full Recovery Model
  2. Simple Recovery Model

SQL Server databases behave very differently depending on the Recovery Model.

Full Recovery Models

Full Recovery Models have the benefit of allowing you to recover a database to a certain point in time, not just to the time backups were made.

The database behaves very differently under this model. It stores all data in the Transaction Log, even after it is committed to the database UNTIL the transaction log is backed up. The transaction log stays the same size but it is emptied and new transactions are piled into the log until the next transaction log backup. This is very important to note: If you have a Full Recovery Model setup for a database, but you don’t perform transaction log backups, then the log will keep filling and never empty. The file (log file) will grow and take up all space. This mistake happens more often than you think. Also, you cannot perform a restore to a point in time without transaction log backups.

I recommend organizations perform transaction log backups every hour or two and a little bit before and after business hours daily. Why bracket business hours? Remember the log empties when backed up, but if transactions are happening while logs are backing up, it will not totally clear and there will be a lot of white space between transactions. This causes fragmentation and other issues. So if backups are performed before or after hours, it gives things a chance to clear out.  Keeping the log as clear as possible is the best weapon to manage space efficiently under the Full Recovery Model.

Simple Recovery Model

Simple Recovery Model only allows you to restore to the time of the last backup (database backup NOT LOG). The behavior is much different than with Full Recovery Model. Transactions do pass through the log but don’t stay there. They are immediately committed to the database. The log never grows under this model.

This model is usually for a low transaction organization that can live with the loss of a day’s work since it restores to the last full backup.

Periodic SQL Server Backups

The second part to a SQL Recovery Plan would be to perform periodic backups in SQL Server. These would be set up to fit the Recovery Model you are using. If you are doing Simple Recovery, then you do not perform transaction log backups. First, you must understand that there are three types of backups:

  1. Full
  2. Differential
  3. Transaction Log

Full

What it does: Takes a full backup of your database.

How it restores: You can restore a full database from just a full backup.

Differential

What it does: Takes only changes since the last full or differential backup.

How it restores: You need the last full backup plus all differentials until now to restore a database.

Transaction Log

What it does: Takes the contents of the log into a backup.

How it restores: You have to restore full, then restore all transaction logs until time you need.

A typical Simple Recovery Plan involves a full backup in the evening and maybe a differential at lunch time.

A Full Recovery Plan involves a full backup in the evening and then two hour-long transaction backups between 6 am and 8 pm.

Finally, it is important to note that old backups must be backed up off the server first and then purged when they are more than X days or weeks old (SQL Serve has built in tools to purge also).

To conclude: You need to have a recovery plan to bring your data back in case of disaster. It is your responsibility to make sure a recovery plan is in place and monitored. You must consider which Recovery Model in SQL will fit in with your recovery plan and have the option to perform backups in SQL Server using built in tools. Please backup, monitor and test. It will save you a lot of pain in the long run.

We can help you set up a Recovery Plan and implement it! Contact Edward McGovern at emcgovern@raffa.com for more information.

This entry was posted in Technology and tagged , , , , . Bookmark the permalink.

One Response to Understanding the Importance of Database Backups for SQL Server

  1. Paul Adam says:

    Incredible post ! This is really nicetable presentation on different Versions and service packs of SQL.I appreciated your way of presenting about SQL. Thanks for posting.Keep it.up.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s