Chapter 16. Database Backup and Restore

by Paul Jensen

IN THIS CHAPTER

• Developing a Backup and Restore Plan

• Why Back Up Your Databases?

• A Typical Backup and Restore Scenario

• Types of Backups

• Setting the Recovery Mode

• Backup Devices

• Media Sets and Families

• Creating Backup Devices with Transact -SQL

• Creating Backup Devices with SQL Enterprise Manager

• Backing Up the Database

• Backing Up the Transaction Log

• Restoring the Database

• Transact-SQL Restore Examples

• Restoring to a Different Database

• Restoring a File or Filegroup

• Restoring to a Point in Time

• Performing a Partial Database Restore

• Restoring the System Databases

• Additional Backup Considerations

One of most important tasks associated with database management is that of performing backups. A backup is a full or partial copy of a database, which can be removed from the server environment for safekeeping. A restore is the process of applying a backup to return a database to a previous point in time. The restore process can also be used to transfer a database to a different location.

SQL Server 2000 builds on the backup and recovery framework introduced with Version 7.0. Enhancements to SQL Server 2000 backup and recovery include the introduction of Recovery models, which simplify balancing data loss against performance, named log marks in the transaction log to allow recovery to specific points of work, and the partial clause in the restore statement which allows a partial database restore to facilitate recovery of lost or corrupted database objects.