SQL 2000 Backup and Restore

This page explains the factors to take into account when you plan a SQL backup strategy.  I give you this as independent advice from someone who visits companies advising them on computer procedures.

• Overall backup strategy
• Write ahead log files
• Log files and restore
• Backup methods

Overall backup strategy

The proof of a successful backup is the restore.  Will your restore do what you think it will?  Gartner's research shows that an amazing 35% of restores do not do what was intended.  My message is that your restore strategy should dictate your backup method.  When you have worked out your backup plan, there is one easy way to test it - restore your database on a test machine.

Write ahead log files

To understand backup you need to be aware of the role of log files in a restore.  What you learn about SQL write ahead logs can be applied to other databases such as Active Directory and exchange.

Let us begin by looking at the role that logs play in a normal database transaction.  SQL uses write ahead logs this means that each UPDATE or INSERT is written to the log first and then later COMMITTED to disk.  If I could emphasise this point, every change to the SQL database is written to the log before it is written to a file on the hard disk.

So my message is keep an eye on these transaction logs both during the backup and when you are about to restore data.

Log files and restore

You will soon see the importance of the logs files in a restore process.  If (when) your database file gets damaged, then you will need to restore the database file from last night's backup tape.  What happens next depend on the logs.  If the logs are intact and in place then SQL will automatically replay the logs on top of yesterday's restored database file.  The result is that your database will now be up-to-date.  It was a real 'ah - ha' moment when I realised the power of the logs to redo all the transactions.

'Best Practice' dictates keeping logs and data files on separate systems, otherwise if you lose the disk then you have to face manually re-entering all the data since the last database backup.  When your frustration wears off you may decide from that day forward to always look after the logs.  Either make sure they are on a different disk from the database, and, or backup the logs every hour.  Differential backups of the log files will not take that much space and will save hours of rework if disaster should strike again.

Have you noticed this strange phenomenon, if you have a perfect backup strategy, nothing goes wrong, but if you backup strategy is faulty lady luck abandons you and disaster strikes at your Achilles heel.

Backup methods

Now that you fully understand the significance of the logs it is time to perfect your backup methods.  Remarkably, all your major decisions come in pairs.

1. Use Enterprise manager or SQL Transact statement?
2. Backup your databases or backup the system databases?
3. Backup Wizard or Maintenance Wizard?
4. Full or Simple Backup?
5. Backup to Tape or Disk?
6. Will this statement backing up the database or the logs?
7. Full or differential backup?
8. Do I truncate the logs or let them grow?

Worried about disk problems try the HardDrive Mechanic

• The Mechanic uses artificial intelligence to diagnose PC problems.
• Once the problem is identified, The Mechanic directs the user to the proper repair procedures.
• The Mechanic then lifts the damaged file system into virtual memory, reconstructs the system and reinserts the newly constructed system onto the hard drive.
• The Mechanic works with Windows Versions: DOS,3.x, 95, 98, NT, Me, 2000, XP and 2000 NTFS.
• For more information Click Here!

1) Enterprise manager or SQL Transact statement

a) This is how to get started with Enterprise manager:

  Programs, Microsoft SQL Server, Enterprise Manager.

b) This is how to get started with Query Analyzer:

  Programs, Microsoft SQL Server, Query Analyzer.
Here is an example backup script:

2) Backup your databases or backup the system databases

Just a reminder to backup the Master and MSDB databases as well as your own.  They are not very big, but the do contain vital information about the other databases and about the Jobs and Alerts that you create.

3) Backup Wizard or Maintenance Wizard

From the Enterprise manager, select your database.  Next go to the Tools menu and choose either Backup Database or Database Maintenance Planner

4) Full or Simple backup?

a) Here is a reminder of how to get started with Enterprise manager:

i) Programs, Microsoft SQL Server, Enterprise Manager.
ii) Right click your database, Properties, Options, Recovery Model = Full (or Simple)

b) Here is the equivalent transact statement :

5) Backup to Tape or to Disk

Firstly I would like to make the point that SQL has its own backup program separate from Windows 2000 or any third party programs.

As expected, you can back up to a tape device; the surprise is that you can also create a disk device and backup to that.  I think of this disk device structure as a virtual tape drive.  Some people then use Backup Exec or ArcServe to backup the backup! 

6) Will my transact statement back up the database or the logs?

Remember that the SQL logs are separate from your database files, so my point is make sure that you backup both.  Each will need its own setting whether you use a transact command or a backup wizard.  Note that the physical database files have .MDF extension (or .NDF if you are using filegroups).  The SQL log files have a .LDF extension whereas most other programs' logs have a .log extension.

7) Full or Differential backup?

Where ever possible make a full backup.  Differential is faster when you do the backup since it just backs up changes; however, differentials are clumsy if you actually use them for a restore.  I say clumsy because you need more tapes and the commands are tricky, so if you use the differential take the time to practice the RECOVERY and NO RECOVERY restore commands.

Make it your policy to make full backups even if it means investing in faster tape drives.  The one exception could be if you are backing up your logs hourly, then differential backups may be acceptable.

8) Do I truncate the logs, or let them grow?

It surprised me that full backups of the database files do not truncate the logs.  Again it is important to understand what is going on.  All becomes clearer when you remember that the database and log have separate physical files, and each can be backed up independently.

What I recommend is that you focus on the log files and decide on your strategy.  When you make a full backup of the logs, then truncating those logs is acceptable - you have all the information in the tapes.  On the other hand with just a full backup of the database but incomplete logs your restore will be out-of-date.

SQL Transact Statements

 *