Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Week 9 – Chapter 8 PowerPoint Presentation
Download Presentation
Week 9 – Chapter 8

Week 9 – Chapter 8

79 Views Download Presentation
Download Presentation

Week 9 – Chapter 8

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