1 / 14

Chapter Overview

Chapter Overview. Understanding Backup Terms, Media, and Devices Backing Up Databases, Files, Filegroups, and Transaction Logs Restoring a User Database Restoring and Rebuilding System Databases. Backup Terms. Backup Media. Tape Excellent for long-term archival storage

phiala
Download Presentation

Chapter Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Overview • Understanding Backup Terms, Media, and Devices • Backing Up Databases, Files, Filegroups, and Transaction Logs • Restoring a User Database • Restoring and Rebuilding System Databases

  2. Backup Terms

  3. Backup Media • Tape • Excellent for long-term archival storage • Relatively slow and limited in capacity • Disk • Relatively fast • Excellent for initial backup and then archive to tape

  4. Permanent Backup Devices • Reusable backup device definitions are stored in the sysdevices table in the master database. • Optional—You can also specify the complete physical name in each backup and restore operation. • In Microsoft SQL Server Enterprise Manager, permanent backup devices are created in the Backup container (within the Management container). • In Transact-SQL, use the sp_addumpdevice system stored procedure.

  5. Backups Using SQL ServerEnterprise Manager • Either directly or with the Create Database wizard. • Specify the backup type and the backup device. • Specify append or overwrite, and choose to verify. • Write a media header if overwriting the media. • Verify the media set and expiration date when appending. • Schedule the backup.

  6. Backups Using Transact-SQL Statements • BACKUP DATABASE Nwind TO BackupDevice • BACKUP DATABASE Nwind TO DiffBackupDevice WITH DIFFERENTIAL • BACKUP DATABASE Nwind FILEGROUP = 'FG1' TO FG1_BackupDevice • RESTORE VERIFYONLY FROM BackupDevice • BACKUP LOG Nwind TO LogBackupDevice • BACKUP LOG Nwind TO LogBackupDevice WITH NO_TRUNCATE

  7. Determining the Restoration Sequence

  8. Full Database Recovery

  9. Restoration of a File or Filegroup

  10. Restoration of a Database to a Different Instance • Create the database in the new instance. • If the database will have a different name, specify Force Restore Over Existing Database. • Specify the file paths for the restored database. • Specify each backup set and its order. (The msdb database in this instance has no record of these backups.)

  11. Point-In-Time Recovery

  12. Restorations Using Transact-SQL • RESTORE DATABASE Nwind FROM BackupDevice WITH NORECOVERY • RESTORE DATABASE Nwind FROM DiffBackupDevice WITH NORECOVERY • RESTORE LOG Nwind FROM LogBackupDevice WITH RECOVERY • RESTORE DATABASE Nwind FILEGROUP = 'FG1' FROM FG1_BackupDevice WITH NORECOVERY • RESTORE LOG Nwind FROM LogBackupDevice WITH RECOVERY

  13. Restoring (and Rebuilding) the Master Database • Start SQL Server in single-user mode: sqlserv –m. • Restore master, msdb, and model from backup (as needed), using either SQL Server Enterprise Manager or Transact-SQL. • If the master database is no longer functioning, use the Rebuildm utility. • Attach or restore user databases if needed.

  14. Chapter Summary • Use permanent backup devices to ease backup and restore tasks. • Use Transact-SQL scripts and schedule periodic backups. • Use SQL Server Enterprise Manager to assist in determining the recovery sequence. • Start SQL Server in single-user mode to recover system databases. • Use Rebuildm to recover from a corrupt system database if SQL Server will not start.

More Related