1 / 20

Week 9 – Chapter 8

Week 9 – Chapter 8. Methods to Backup Databases Types of data to be backed up Recovery models Recovery methods. SQL 710. Methods to Back up Data. Maintenance Wizard (Chapter 7) to create maintenance plan with scheduled backups

bat
Télécharger la présentation

Week 9 – Chapter 8

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. Week 9 – Chapter 8 • Methods to Backup Databases • Types of data to be backed up • Recovery models • Recovery methods SQL 710

  2. Methods to Back up Data • Maintenance Wizard (Chapter 7) to create maintenance plan with scheduled backups • Enterprise Manager to schedule job to perform backup or to perform unscheduled backup as required • T-SQL commands • Specialized packages such as Backup Exec or Arcserv (not discussed here) SQL 710

  3. Prevent Loss of Data • Have a strategy: • To minimize data loss (malicious use of delete, update statement, viruses, natural disaster, theft) • To recover lost data • To restore data with minimal cost and impact • Backup regularly: • Backup frequently if your database is OLTP • Backup less frequently if your database is OLAP SQL 710

  4. Database Recovery Models • Set Database Recovery Model: • Full Recovery Model • Bulk logged recovery model • Simple Recovery Model • Modify a database recovery model • Alter database pubs • Set recovery to bulk_logged SQL 710

  5. Backups • SQL Server allows backups to occur while users continue to work with the database • Backs up original files and records their locations • Captures in the backup all database activities that occur during the backup process SQL 710

  6. Backups (ctd) • Who can perform backup? • Members of the sysadmin fixed server role • Members of the db_owner and db_backupoperators fixed database roles • Where to store backup? • Hard disk file • Tape • A location identified by a Named Pipe (3rd party software package) SQL 710

  7. When to backup System Databases • After modifying the master database: • Using CREATE DATABASE, ALTER DATABASE or DROP statement • Executing certain Stored Procedures • After modifying the msdb database • After modifying the model database SQL 710

  8. When to backup User Databases • After creating a database • After creating an index • After creating a transaction • After performing un-logged operations: • BACKUP WITH TRUNCATE_ONLY OR NO_LOG OPERATIONS • SELECT INTO statement SQL 710

  9. Restricted Activities during backup • Creating or modifying database • Performing autogrow operations • Creating indexes • Performing non-logged options • Shrinking a database SQL 710

  10. Create a Backup device • A backup file that is created before it is used for a backup is called a backup device • Why create permanent backup devices? • To reuse backup files for future backups • To automate the backup SQL 710

  11. Create a Backup device(ctd) • Use sp_addumpdevice system procedure: • Specify a logical name • Logical and physical Names are stored in the sysdevices system table • Example: • Use master • Exec sp_addumpdevice ‘disk’ , ‘mybackupfile’, ‘c:\Backup|mybackupfile.bak’ SQL 710

  12. Perform Backup without backup device • Why create backup without backup device? • To perform one time backup • To test backup operation that you plan to automate • How to use backup database statement: • Specify the media type (disk, tape, or Named Pipe) • Specify the complete path and full Name • Example: • Use master • Backup database Northwind • To Disk = ‘c:\temp\mycustomers.bak’ SQL 710

  13. Types of Backup Methods • Full database backup • Differential backup • Transaction log backup • File or File group backup SQL 710

  14. Full Database Backup • Provides a baseline • Backs up original files, objects and data • Backs up portions of the transaction log • Example: • Use master • Exec sp_addumpdevice ‘disk’, ‘NwindBac’ , • ‘D:\mybackupdir\Nwindbac.bak’ • Backup database Northwind to NwindBac SQL 710

  15. Full Database Backup Options WITH INIT: overwrites any previous backup on that file WITH NOINIT : appends the full database backup to the backup file. Any previous backup left intact. SQL 710

  16. Differential database backup • Use on frequently modified databases • Requires a full database backup before • Backs up database changes since the last full database backup • Saves time in both backup and restore processes • Example: • Backup Database Northwind • Disk = ‘D:\Mydata|Mydiffbackup.bak’ • WITH DIFFERNTIAL SQL 710

  17. Transaction log backup • Requires a Full database backup • Backs up all database changes from the last BACKUP LOG statement to the end of the current Transaction log. • Truncates the transaction log • Example: • Use master • Exec sp_addumpdevice ‘disk’, ‘Nwindbaclog’, • ‘D:\Baclup\Nwind backuplog.bak’ • Backup log Northwind To NwindBaclog SQL 710

  18. Backup using No-truncate Option • No-truncate option: • Saves the entire Transaction log even if the database is inaccessible • Doesn’t purge the Transaction log of committed Transactions • Allows data to be recovered up to time of system failure SQL 710

  19. Clear the Transaction log • Use Backup statement to clear transaction log • Use truncate only or no_log option • Can’t recover changes • Is not recorded changes SQL 710

  20. Database file or filegroup backup • Use on very large databases • Backup the database files individually • Ensure that all database files in File group are backed up • Back up transaction log • Example: • Backup database phoneorders • File = Orders2 To orderbackup2 • Backup log phoneOrders to orderlog SQL 710

More Related