Skip navigation FileNET logo
  Help for Backup and Restore  
  Search  |  Index  
Backup and restore guidelines
Concepts
  Backup strategies
  Backup types
    NTBackup
    SQL Server
    Enterprise
  Best practices
What to back up
Prepare backup tapes
Backing up the system state
Backing up Process Engine
Backing up Process Analyzer
Backing up databases
Restoring the system state
Restoring Process Analyzer
Restoring databases
   

SQL Server backup

SQL Server provides a backup tool that you can start through SQL Server's Enterprise Manager using the Backup Database option for a database. You can also start this through Transact-SQL command BACKUP in the Query Analyzer. SQL Server supports four different backup types, including:

NOTE Other database applications have similar backup types.

Complete database backup

A complete database backup copies all data pages and transaction log pages of a selected database on a backup set. It is the same as the normal backup for file systems.

  • Advantages: Easy to restore. Since the backup set contains all information needed to reconstruct a database, you only need to restore from the latest set.

  • Disadvantages: Takes the longest time and most storage resources for each backup.

  • Example: Perform a complete database backup as the first backup set, or if the database is small, or after a major change in the database.

Differential backup

A differential backup copies all pages of a selected database that have been modified since the last complete backup. It is similar to the differential backup for file systems.

  • Advantages: Since the backup set consists only of pages modified since the last complete backup, a performing a differential backup creates a relatively small backup set and requires a short time to perform the backup.

  • Disadvantages: Creates a larger backup set compared to transaction log backups.

  • Example: Make a complete backup every Sunday; make a differential backup on all the other days.

Transaction log backup

A transaction log backup copies only the transaction log file since the last complete or transaction log backup. It is similar to the incremental backup for file systems.

  • Advantages: Since the backup set consists only of new and updated transactions since the last complete or log backup, performing a transaction log backup creates the smallest backup set and requires the shortest time to perform the backup.

  • Disadvantages: When restoring transaction logs, you must use the sets that contain all transaction log backups after a complete or differential backup. Using the SQL Enterprise Manager to restore the transaction log backup on the same server automatically selects all the necessary sets.

  • Example: Make a complete backup every Sunday; back up the log every 4 hours, except during a complete backup.

File or file group backup

A file or file group backup backs up individual files or file groups within a database. This can help when backing up very large databases when you don't have enough time to perform backups.

  • Advantages: Allows you to selectively back up individual parts of a database.

  • Disadvantages: Requires careful planning to ensure you can recover the database. For example, if any tables or indexes span multiple file groups, you must back up these file groups together.

  • Example: Make a backup for file group 1 on Mon, Weds, Fri; make a backup for file group 2 on Tues, Thurs, Sat; make a log backup after each file group backup.