Download
chapter overview n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter Overview PowerPoint Presentation
Download Presentation
Chapter Overview

Chapter Overview

80 Views Download Presentation
Download Presentation

Chapter Overview

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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.