Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 27, 2005

Backing up and Restoring Databases in SQL 2000

By Steven Warren

If you are a Database Administrator (DBA), I have no doubt in my mind that you have been called away from home, dinner, or just plain in the middle of the night to answer a page on a disaster with your database(s). In this field, it is the nature of the beast to get called away at the most inappropriate time. Aren't you glad you backed up your database(s)? Or, did you? In this article, we will introduce you to the different types of backup strategies available in SQL Server 2000.

Have a Plan?

Before developing your backup strategy, you need to develop a plan. What I mean by this is that you need to evaluate your specific company and decide on what the maximum amount of downtime is. Once this is established, you can decide on the appropriate course of action.

Backups 101

In backups 101, we explore the different types of backup strategies and how to back them up using the graphical user interface (GUI) and Transact SQL (TSQL).

Full Backups

A full backup is the easiest type of backup to implement in SQL Server. This type of backup takes a complete picture of your database. This picture 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.

To backup a database using Enterprise Manager, expand the SQL Server to display your databases. Right-click on the database and select All Tasks | Backup Database. Select your Backup Type (Figure A). If you have a tape drive, you can choose whether to backup to disk or tape. If it is the first backup of your database, you will have to create a backup device or file.

Figure A.

A backup device is simply a location that stores your backups. A backup file can also hold multiple backups but a drawback is that the file is not created until the backup starts. To create a backup device, choose New Backup Device, define your path, and click Ok.

Note: A full backup backs up your users but not your logins. If you want to backup your logins, remember to backup 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. Please see a more detailed explanation of this stored procedure in SQL Books Online.

You can choose to overwrite your backup or append to your backup. This means that you can add multiple backups to your device or completely overwrite your backups with one backup. If you plan to schedule your backup, select the schedule checkbox and choose Change. You can choose from the following types:

  • Start Automatically when SQL Server Agent Starts
  • Start Whenever CPU becomes Idle
  • One Time
  • Recurring

On the Options tab, you can also 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
  • Initialize and Label Media

Backups in T-SQL

You can also backup a database using T-SQL; it has options that are not available when backing up data in Enterprise Manger. The syntax is as follows:

BACKUP DATABASE { database_name | @database_name_var } 
TO < backup_device > [ ,...n ] 
    [ 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, please see the description in SQL Books Online. In the following example, I will backup the Pubs database (Figure B).

Figure B.

Note: By adding NOINIT, I appended the information to the end of the backup and by adding Stats to the argument, I received a percentage of the backup completion.

Differential Backups

A differential backup is a backup that only backs up the data that has changed since the last full backup. These backups are generally smaller and can be used frequently since they run much faster than a full backup. To perform a differential backup, choose the differential backup radio button when backing up the data in Enterprise Manager.

To perform a differential backup using Transact SQL, use the Backup command but add the differential argument (Figure C.)

Figure C.

Transaction Log Backups

A transaction log backup will backup all transactions that occur in the database and purge or clean up the log after the backup completes. Using a transaction log backup is what truly gives you point-in time-recovery for a database. To perform transaction log backups, choose the transaction log backup option from Enterprise Manager.

Note: You must be using Full or Bulk-Logged Recovery Models to perform transaction log backups. This will be discussed later in the article.

Backing up a transaction log

To perform a transaction log backup 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 definition of each option, please see the description in SQL Books Online. To perform a transaction log backup using Transact SQL, use the Backup Log command (Figure D).

Figure D.

File Group Backups

A file group backup lets you backup individual files or file groups. If you are unfamiliar with this, it basically allows you to backup a large database by spreading your backups out over time.

Using a Database Recovery Model

The database recovery model is a new feature that was added to SQL Server 2000. This new model allows you to control your backups and your disaster recovery options. Options from SQL Server 7.0 like select into/bulk copy and trunc. log on checkpoint have been replaced in SQL 2000 by either choosing Simple, Full, or Bulk-Logged recovery models.

Note: To select a database recovery model, right-click on the database and choose properties. Click the Options tab and select your recovery model.


By selecting the Simple Recovery Model, you are basically choosing the trunc. log on checkpoint option that appeared in SQL Server 7.0. This recovery plan always truncates your transaction log and constantly removes your 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.

Note: If you try to backup the transaction log when using a Simple recovery model, you will receive an error stating, "The backup log is not allowed while the recovery model is Simple."

Full and Bulk

These options add an administration and space burden but provide the maximum protection for your data.

Note: The Full Recovery model provides you with better flexibility for recovering databases to an earlier point-in-time and the Bulk-Logged model provides higher performance and lower log space usage.

Now that we have introduced you to backups 101, explore the possibilities and begin to get familiar with the basics.

» See All Articles by Columnist Steven S. Warren

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM