• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Tips and Tricks HQ

  • Home
  • Blog
  • Projects
    • All Projects
    • Simple WP Shopping Cart
    • WP Express Checkout Plugin
    • WP Download Monitor
    • WP Security and Firewall Plugin
    • WP eStore Plugin
    • WP Affiliate Platform
    • WP eMember
    • WP Lightbox Ultimate
    • WP Photo Seller
  • Products
    • All Products
    • Checkout
  • Support
    • Support Portal
    • Customer Only Forum
    • WP eStore Documentation
    • WP Affiliate Software Documentation
    • WP eMember Documentation
  • Contact

Database Recovery Techniques

You are here: Home / Tech Tips / Database Recovery Techniques

Last updated: July 28, 2010





Preventing data loss is one of the most critical issues involved in managing database systems. Data can be lost as a result of many different problems:

  • Hardware failures
  • Viruses
  • Incorrect use of UPDATE and DELETE statements
  • Software bugs
  • Disasters, such as fire or flood

To prevent data loss, you can implement a recovery strategy for your databases. This article explains the traditional database recovery techniques that are out there.

If you are only concerned about backing up your WordPress blog then simply use a database backup plugin explained in the Must Use Plugins List article.

Full Database Backups

A very common backup strategy is to back up the whole database in a predefined time series (once each night, for instance). With such a backup strategy, it is possible to recover a database to the state it had when the last backup occurred. This strategy is implemented by using full database backups. A full database backup contains all data and database Meta information needed to restore the whole database, including full-text catalogs. When you restore a full database backup, it restores all database files yielding data in a consistent state from the time the backup completed.



Using Differential Backups

The main advantage of a full database backup is that it contains all the data needed to rebuild the entire database. But this advantage can also be a disadvantage. Consider a database where full database backups are performed each night. If you need to recover the database, you always have to use the backup from the previous night, resulting in the loss of a whole day’s work. One way to reduce the potential period of time that can be lost would be to perform full database backups more often. But this itself can be a problem. Because all data and parts of the transaction log are written to the backup device, it can be very time-intensive to make a backup. Also, you need a lot of storage space to hold these backups, and a full backup can decrease the performance of your database as a result of the large amount of I/O it requires. Wouldn’t it be better to perform one full database backup at night and only take backups of data changes made during the day? This sort of functionality is provided by the differential backup.
The differential backup stores only the data changes that have occurred since the last full database backup. When the same data has changed many times since the last full database backup, a differential backup stores the most recent version of the changed data. Because it contains all changes since the last full backup, to restore a differential backup, you first need to restore the last full database backup and then apply only the last differential backup.

Performing Differential Backups

Performing a differential backup is very similar to performing full database backups. The only difference is that you state in the WITH option of the backup that you want to perform a differential backup. The syntax of the BACKUP DATABASE statement to perform a differential backup of AdventureWorks to a physical device, overwriting other existing backups on the backup device, is as follows:

USE master;
GO
BACKUP DATABASE AdventureWorks
TO DISK=’t:\adv_diff.bak’
WITH INIT, DIFFERENTIAL;

Using Transaction Log Backups

With the combination of full database and differential backups, it is possible to take snapshots of the data and recover them. But in some situations, it is also desirable to have backups of all events that have occurred in a database, like a record of every single statement executed. With such functionality, it would be possible to recover the database to any state required. Transaction log backups provide this functionality. As its name suggests, the transaction log backup is a backup of transaction log entries and contains all transactions that have happened to the database. The main advantages of transaction log backups are as follows:

  • Transaction log backups allow you to recover the database to a specific point in time.
  • Because transaction log backups are backups of log entries, it is even possible to perform a backup from a transaction log if the data files are destroyed. With this backup, it is possible to recover the database up to the last transaction that took place before the failure occurred. Thus, in the event of a failure, not a single committed transaction need be lost.

Combining Transaction Log and Differential Backups

Another possible backup strategy is to combine full database, differential, and transaction log backups. This is done when restoring all transaction log backups would take too much time. Because restoring from a transaction log backup means that all transactions have to be executed again, it can take a great deal of time to recover all the data, especially in large databases. Differential backups only apply data changes, which can be done faster than re-executing all transactions.
To recover a database when you have a combined backup strategy, you need to restore the last full database backup, the last differential backup, and then all subsequent transaction log backups.

The Full Recovery Model

As mentioned before, you need to tell SQL Server in advance which backup strategy you plan to implement. If only full database and differential backups are used, the database has to be set to the simple recovery model. If you also want to use transaction log backups, the recovery model must be set to FULL (or BULK_LOGGED). The full recovery model tells SQL Server that you want to perform transaction log backups. To make this possible, SQL Server keeps all transactions in a transaction log until a transaction log backup occurs. When the transaction log backup happens, SQL Server truncates the transaction log after the backup is written to the backup device. In simple mode, the transaction log is truncated after every checkpoint, which means that committed transactions (which are already written to the data files) are deleted from the transaction log. Thus, in simple mode, transaction log backups cannot be created.
To set the recovery model to FULL, use the ALTER DATABASE statement again. The following code sets the recovery mode of AdventureWorks database to FULL:

USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
To set recovery model to SIMPLE, use the following command
USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO

Basically, try to be prepared so you don’t get caught with your pants down like this 🙂

Related Posts

  • How to import WordPress SQL database backup file without having ‘create new database’ privileges in phpMyAdmin
  • What Would You Do If Somehow You Lost all Your Blog’s Content?

Tech Tips Computer Science,  Database,  database import,  Database Recovery,  Disaster Recovery

Reader Interactions

Comments (5 responses)

  1. Jake says:
    August 20, 2010 at 1:51 pm

    Where were you 6 months ago to remind me on this one? ^^
    Thanks! And trust me… It pays to back up everything important. I remember losing 400GB of data simply because I hit the wrong button while changing OS… That hurt

  2. QQ says:
    August 11, 2010 at 4:49 am

    backup strategy is to combine full database, differential, and transaction log backups. This is done when restoring all transaction log backups would take too much time.

  3. Webmaster Blog says:
    July 30, 2010 at 2:17 pm

    I backup all my files and databases every week. I use export in the phpmyadmin, it is very useful. After that you can import and it is all there. If there is a major hacker attack that deletes even the backups of my hosting, I will be set back no more than 1 week 🙂

  4. Corrine says:
    July 28, 2010 at 11:42 pm

    Nice post! In this fast moving world, data is the heart and soul of any business. It is very important to have a clear disaster recovery path in place.

  5. anna says:
    July 28, 2010 at 4:04 am

    Backing up database in today’s computer realm is one of the most important directions. It is recently highly required by people who care about their business assets and individual data security.

Leave a Reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Featured & Popular Articles

Video Answers to Top WordPress QuestionsWordPress Optimization Tips and Tricks for Better Performance and SpeedEssential WordPress Security Tips - Is Your Blog Protected?WordPress Simple PayPal Shopping Cart PluginTop 15 Search Engine Optimization (SEO) Techniques I Forget to DoList of the Best and Must Use WordPress PluginsHow do I Start a Blog and Make Money Online?Good Domain Name Picking Tips for Your Blog SetupFind Out Which WordPress Web Hosting Company Offers the Cheapest and Reliable Web Hosting Solution

Featured WordPress Plugins

wordpress estore plugin
wordpress membership plugin
WP Express Checkout Plugin
WordPress Lightbox Ultimate Plugin
WordPress photo seller plugin
wordpress affiliate plugin

Recent Posts

  • Accept Donations via PayPal from Your WordPress Site Easil [...]
  • Buy Now Button Graphics for eCommerce Websites [...]
  • Subscription Button Graphics for eCommerce Websites [...]
  • Adding PayPal Payment Buttons to Your WordPress Sidebar Ea [...]
  • PayPal QR Codes [...]

Comment & Socialize

  • @Rodrigo Souza, Thank you f ...
    - admin
  • The example for 'slm_add_ed ...
    - Rodrigo Souza
  • @Ron, All the valid transac ...
    - admin
  • Hello, when people have sel ...
    - Ron
  • We have hte following featu ...
    - admin

Check out our solutions

View our WordPress plugin collection and start using them on your site.

Our WordPress Solutions

Footer

Company

  • About
  • Privacy Policy
  • Terms and Conditions
  • Affiliate Login

Top WordPress Plugins

  • Simple Shopping Cart
  • PayPal Donations
  • WP Express Checkout
  • WP eStore
  • WP eMember

Blogging Tips

  • How to Start a Blog
  • Selecting a Good Domain
  • Cheap WP Hosting
  • WP Video Tutorials
  • Simple SEO Tips

Search


Keep In Touch

Copyright © 2023 | Tips and Tricks HQ