Most database administrators (DBAs) know what it’s like to be called away from home or dinner or even awakened in the middle of the night to answer a page or a cell-phone call concerning some type of database disaster. These are the times you’re glad that you have a systematic backup plan. Of course, conversely, if you don’t have a good backup plan, these are the times you regret it.

In this article, we will discuss various types of backup strategies and demonstrate how to implement each of these types of backups using Enterprise Manager, the graphical user interface (GUI), and Transact SQL (T-SQL) in Microsoft SQL Server 2000.

Put together a backup plan
Before developing your backup strategy, you need to develop a plan of action. Evaluate your company and ask, “What is the maximum amount of downtime we can live with?” Once this is established, you can decide on the appropriate course of action by matching your company’s needs to the appropriate level of database backup.

Full backups
A full backup is the easiest type of backup to implement in SQL Server. It takes a complete picture of your database that includes backing up users and permissions. In addition, this backup can occur while transactions are still occurring in your system. When the backup begins, the date is recorded, the data pages are backed up, and all transactions that occur while the backup runs are appended to the backup.

Author’s note
A full backup backs up your users but not your logins. If you want to back up your logins, remember to back up the Master database. Furthermore, if you ever restore your database to a different server, you must synchronize your logins by using the sp_change_users_login. For a more detailed explanation of this stored procedure, see SQL Books Online.


Using Enterprise Manager
To implement a full backup using Enterprise Manager, expand the SQL Server to display your databases. Right-click on the database and select All Tasks | Backup Database to access the options shown in Figure A.

Under Backup, choose Database – Complete. Then, select a destination (tape or disk). If this is the first backup of your database, you will have to create a backup device or file. A backup device is simply a location that stores your backups. A backup file can also hold multiple “backups.” To create a backup device or file, start by clicking Add. Next, select either File Name or Backup Device. (Figure B shows the dialog box you’ll see if you choose Backup Device.) Then, just define the appropriate path and click OK. Once you’ve created the device or file, you can select it from the Destination list box.

The SQL Server Backup dialog box also includes options to append to a backup or overwrite it. This means that you can add multiple backups to your file or device or overwrite previous backups with the most current one. If you want to schedule your backups, select the Schedule check box and then click the button that has three dots on it. In the Edit Schedule dialog box, shown in Figure C, make your scheduling specifications.

You can choose from the following options:

  • Start Automatically When SQL Server Agent Starts
  • Start Whenever CPU(s) Become Idle
  • One Time
  • Recurring

You can use the Change button to set a time for recurring backups.

Enterprise Manager also allows you to set a variety of other options via the SQL Server Backup dialog box. If you click on the Options tab, you can choose the following:

  • Verify Backup Upon Completion
  • Eject Tape After Backup
  • Remove Inactive Entries From Transaction Log
  • Check Media Set Name And Backup Set Expiration
  • Backup Set Will Expire (You supply an expiration date.)
  • Initialize And Label Media

Using T-SQL
You can also back up a database using T-SQL, which offers options that Enterprise Manager doesn’t. The syntax for a full backup is as follows:
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,…n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { ‘text‘ | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { ‘text‘ | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ =percentage ] ]

For a definition of each option, see the description in SQL Books Online.

Figure D shows an example of how I backed up the Pubs database. By adding NOINIT, I appended the information to the end of the backup. By adding Stats to the argument, I received an output of the percentage of the backup completed.

Differential backups
A differential backup backs up only the data that has changed since the last full backup. These backups are generally smaller than a full backup and can be used frequently since they run much faster. To perform a differential backup in Enterprise Manager, choose the Database-Differential option in the SQL Server Backup dialog box (which we saw in Figure A).

To perform a differential backup using T-SQL, use the Backup command but add the differential argument, as shown in Figure E.

Transaction-log backups
A transaction-log backup will back up all transactions that occur in the database and purge or clean up the log after the backup completes. Using a transaction-log backup gives you point-in-time recovery for a database. To perform transaction-log backups with Enterprise Manager, choose the Transaction Log option in the SQL Server Backup dialog box.

Keep in mind that you must be using Full or Bulk-Logged Recovery Models, which will be explained below, in order to perform transaction-log backups.

To perform a transaction-log backup with T-SQL, use the following syntax:
BACKUP LOG { database_name | @database_name_var }
{
    TO < backup_device > [ ,…n ]
    [ WITH
        [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
        [ [ , ] DESCRIPTION = { ‘text‘ | @text_variable } ]
        [ [ ,] EXPIREDATE = { date | @date_var }
            | RETAINDAYS = { days | @days_var } ]
        [ [ , ] PASSWORD = { password | @password_variable } ]
        [ [ , ] FORMAT | NOFORMAT ]
        [ [ , ] { INIT | NOINIT } ]
        [ [ , ] MEDIADESCRIPTION = { ‘text‘ | @text_variable } ]
        [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
        [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
        [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
        [ [ , ] NO_TRUNCATE ]
        [ [ , ] { NORECOVERY | STANDBY =undo_file_name } ]
        [ [ , ] { NOREWIND | REWIND } ]
        [ [ , ] { NOSKIP | SKIP } ]
        [ [ , ] { NOUNLOAD | UNLOAD } ]
        [ [ , ] RESTART ]
        [ [ , ] STATS [ =percentage ] ]

For a detailed definition of each option, please see the description in SQL Books Online. You can perform the transaction-log backup by using the Backup Log command, as shown in Figure F.

File-group backups
A file-group backup lets you back up individual files or file groups. It basically allows you to back up a large database by spreading your backups out over time. This is a more granular type of backup that allows an admin to take a custom approach or to simply fill in the gaps of other backup methods.

Using a database recovery model
The database recovery model is a new feature that was added to SQL Server 2000. This model makes it easier to control your backups and disaster recovery options. SQL Server 7.0 options like selectinto/bulkcopy and trunc.logoncheckpoint have been replaced in SQL 2000 by the Simple, Full, and Bulk-Logged recovery models. Let’s take a look at the differences between these models.

Specifying a recovery model
To select a database recovery model, right-click on the database and choose Properties. Click the Options tab and select your recovery model.


Simple recovery model
The Simple recovery model is similar to the trunc.logoncheckpoint option in SQL Server 7.0. This recovery plan always truncates your transaction log and constantly removes transactions that have been committed. Because of this, the transaction log cannot be backed up, which leaves you with only two backup options: full and differential.

If you try to back up the transaction log when using a Simple recovery model, you will receive this error message: “The backup log is not allowed while the recovery model is Simple.”

Full and Bulk-Logged recovery models
The Full and Bulk-Logged options add an administration and space burden but provide the maximum protection for your data. The Full recovery model provides you with better flexibility for recovering databases to an earlier point in time. The Bulk-Logged model provides higher performance and lower log space usage than Full recovery but does not provide as much protection.