Any suggestions? Export (0) Print
Expand All

Backup and Restore Operations

SQL Server 2000

Microsoft® SQL Server™ supports various types of backups to be used separately or in combination. The recovery model you choose will determine your overall backup strategy, including the types of backups available to you. For more information, see Designing a Backup and Restore Strategy and Using Recovery Models.

The following table illustrates the types of backups that are available for each recovery model.

Backup Type
Database differential Transaction
File or file differential
Simple Required Optional Not allowed Not allowed
Full Required
(or file backups)
Optional Required Optional
Bulk-Logged Required
(or file backups)
Optional Required Optional

Backups are created on backup devices, such as disk or tape media. With SQL Server, you can decide how you want to create your backups on backup devices. For example, you can overwrite outdated backups, or you can append new backups to the backup media. For more information, see Managing Backups.

Performing a backup operation has minimal effect on running transactions, so backup operations can be run during normal operations.

Note  Creating or deleting database files is not possible when the database or transaction log is being backed up. If you attempt to create or delete a database file while a backup operation is in progress, the create or delete will fail. If you attempt to start a backup operation while a database file is being created or deleted, the backup operation will wait until the create or delete is completed or the backup operation times out.

See Also

Selecting a Recovery Model

Analyzing Availability and Recovery Requirements

Planning for Disaster Recovery

© 2016 Microsoft