SQL Server 2000 Administration in 15 Minutes a Week: Database Backups

Friday Jun 14th 2002 by Michael Aubert

Michael Aubert's seventh article in the SQL Server 2000 Administration in 15 Minutes a Week series looks at how to backup databases. Topics include a discussion of why backups are important, the various types of database backups, Database Recovery Models, and a step-by-step guide to making a backup.

Welcome to the seventh article in my series SQL Server Administration in 15 Minutes a Week. Last week we learned what T-SQL is and we also saw how the transaction logs are used to track changes in the database. This week we are going to look at how to backup our databases. The topics for this article include:

-- Why Backups are important
-- Database Backup Types 
-- Database Recovery Models
-- Making a backup

Why Backups are important

One of the most important tasks you will face as a DBA is performing backups. Although backups are certainly not the most interesting part of the job, they are probably the single most important. If something goes wrong it's the DBA's job to get the server back up and running as quickly as possible. Loss of productivity or, even worse, loss of data can be very expensive for a company. Let's look at the most common question I'm asked when talking about backups.  

Why not just use a RAID configuration that has mirroring to provide protection? RAID is certainly the first line of prevention for data loss. Depending on the RAID configuration you use, one or even several hard drives can fail before the data is lost. Additionally, the use of hot swappable and hot standby drives can be used to allow the server to continue without ever having to be taken offline in the event a hard drive fails. The key thing to notice here is that RAID can protect you if a hard drive fails, but what happens if a fire or natural disaster occurs? What do you do if your database files become corrupted due to hardware or software errors? Or what happens if a user deletes data that they later need? A RAID configuration will not help you if any of these events occurs. 

Remember, you can always replace the server, but the data on that server is extremely difficult, if not impossible, to recover.

Page 2: Database Backup Types

 » See All Articles by Columnist Michael Aubert

Database Backup Types

SQL Server has three main backup types: Full, Differential, and Log. In addition to the main three that backup the whole database, there are also a few additional types of backups that can be used to backup a single file or a filegroup. 

Full Backups - a full backup copies all the extents from the database. If you need to restore your database and are using full backups, you only need to apply the last full backup. However, full backups are the slowest of all the backup types.

Differential Backups - a differential backup only backs up the extents that have changed from the last full backup. If you need to restore your database and are using differential backups, you will need to apply the last full backup and then the last differential backup you have made. Differential backups are faster to create, but they take longer to restore than a full backup because you have to apply the full backup and then the differential backup.

Log Backups - a log backup is used to backup the transaction log from the last full or transaction log backup. You may or may not be able to make log backups -- it depends on the  recovery model you are using. If you need to restore your database and you are using full and log backups, you would need to restore the last full backup and all (in order) the transaction log backups.

One thing to note about backups is that they are done with the database online. This is called a "fuzzy backup" because the backup is not from a single point in time. The backup copies extents from the database and if any modifications are made, the backup just continues copying. To keep consistency, full and differential backups capture the portion of the log file from the point the backup started to the end of the backup.

SQL Server can backup to a file on your hard drive, a file on a network drive, a tape drive, or a named pipe.

Page 3: Database Recovery Models

 » See All Articles by Columnist Michael Aubert

Database Recovery Models 

Before jumping into a discussion on recovery models we need to first take a look back to our discussion of transaction logs from last week. Remember that all modifications made to the database are recorded in the transaction log. In the event of a failure (such as a power outage or blue screen) the transaction log can be used to reapply the changes to the database. Additionally, checkpoints are used to write all pages in memory back to the hard disk, lowering the time needed to recover the database. So once a checkpoint occurs and all the data pages are written to disk, why do we need to store information about transactions? This is where the recovery model plays a role. 

Each database running on a SQL Server can have one of three recovery models: Full, Bulk_Logged, and Simple. 

In the full recovery model, every change made to the database is logged -- all UPDATE, DELETE, and INSERT statements are logged. Additionally, certain bulk operations, such as BULK INSERT, that are used to make many modifications quickly are also logged in their entirety (i.e. each individual row added by the BULK INSERT command would be logged).

The full recovery model provides the most options in the event a data file is corrupted.
When a transaction is logged and the database is in full recovery mode, the transaction stays in the log until it is backed up. Once the database is backed up, the space from the old transactions are freed and can then be used to log new transactions. Because all transactions are backed up, Full database backups make it possible to restore a backup to a "point in time" by applying only the transactions up to that point. For example, we could restore a full backup and then restore all our log backups up to a certain point before data was deleted.

If full recovery mode tracks all changes made to the database and allows us to restore transactions to a point in time, why not just use it all the time? Because all operations are logged in their entirety, you could end up with some big log files. Also, commands like BULK INSERT will be slowed down because every modification must be logged.

The bulk_logged recovery model is a lot like the full recovery mode with a few benefits and tradeoffs. Like the full recovery mode, the bulk_logged recovery model also logs all UPDATE, DELETE, and INSERT statements. However, bulk_logged only records that the operation took place for certain commands. These commands include BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT, and UPDATETEXT. The bulk_logged recovery model is also like the full recovery model in that they do not reuse (or overwrite) log space until the transactions have been backed up.

Unlike the full recovery model, if a transaction log includes bulk operations you can not restore that log to a point in time, you must restore to the end of the log. Also, a log backup of the database may be much larger because in the bulk_logged recovery model, log backups must copy all the extents that have changed.

The benefit of the bulk_logged model is that the log file(s) for the database can be smaller if you use many bulk operations. Also, bulk operations are much faster because only the fact that the operation occurred needs to be recorded, not every modification to the database.

The last type of recovery model is the simple recovery model. Unlike the full and bulk_logged recovery models, simple recovery does not backup transaction logs. In this mode transaction logs are frequently truncated (truncation is the process of removing old transactions from the log) automatically. The simple recovery model can use full and differential backups.

In SQL Server Enterprise Edition the model database is set to the full recovery mode. Because all our databases are basically a copy of the model database, the recovery model for the databases we create is also full. You can change the model database's
recovery model to another type if you want all new databases you create to start with another recovery model.

To change the recovery model for a database in Enterprise Manager, right click the database, select properties, and then choose the options tab. You can also use the ALTER DATABASE statement to change the recovery model.

One last topic in the database recovery models section is the change from one model to another. Unlike previous versions of SQL server, SQL Server 2000 can switch between full and bulk_logged recovery models as needed. For example if you perform bulk operations infrequently, you can use the full  recovery model and switch to bulk_logged as needed to have the bulk operations perform faster. The log backup will be larger and take longer, however, when you do this.

Switching between the simple recovery model and another recovery model is not so "simple." In order to have the change take place you will need to make a full backup of your database. You should only use the simple recovery model for development databases -- production databases should use the full or bulk_logged recovery models.

Page 4: Making a Backup

 » See All Articles by Columnist Michael Aubert

Making a backup

To make a backup manually, you have plenty of options. We are going to look at three ways you can create a backup.

First, right click the database you would like to backup in Enterprise Manager, select All Tasks, and then click Backup Database...

You can also use the Create Database Backup Wizard by selecting the Tools menu in Enterprise Manager, select Wizards..., expand Management, and select Backup Wizard.

The third way to backup your database is by using the BACKUP statement. For more information on the BACKUP statement, see the SQL Server books online.

We are going to talk more about backups next week, but if you would like to read more about this topic, have a look at this link: http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_9zcj.asp.

That will do it for this week. Next week we will talk about creating a backup strategy as well as restoring backups.

As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.


» See All Articles by Columnist Michael Aubert

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved