1 / 45

Chapter One

Chapter One. Performing Disaster Recovery Operations. Objectives. Understand backup operations and recovery models Create database devices Create full database, differential database and transaction log backups Use backups to restore a database. Objectives. Configure log shipping

gurit
Download Presentation

Chapter One

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 One Performing Disaster Recovery Operations

  2. Objectives • Understand backup operations and recovery models • Create database devices • Create full database, differential database and transaction log backups • Use backups to restore a database

  3. Objectives • Configure log shipping • Use the Database Maintenance Plan Wizard to create a disaster recovery plan • Ensure the consistency of databases using DBCC T-SQL statements

  4. Planning for Disaster • With improvements in reliability of hardware and software, system meltdowns are less frequent • Still, hardware will fail and users will make mistakes like deleting important information by accident • Only recourse is to plan for such events and minimize the effects on an organization using SQL Server 2000 • SQL Server 2000 offers a flexible backup architecture that allows database administrators to execute backup operations that meet the data availability requirements of any database

  5. Backup Operations • Issues to consider when deciding how to use the backup operation provided with SQL Server 2000: • The time it takes to perform the operation (especially when the operation is executed while users are accessing the database) • The amount of data loss that is acceptable within the system • The time it would take to restore the database to an operational status in the event of a failure

  6. Backup Operations • Full database backup • Creates exact copy of all data in a database • Takes most time to perform since all information is copied • Requires more storage space • Knowing the volume of data in a database before performing the backup is important when working in an environment where storage space is limited • The sp_spaceused system-stored procedure is used to determine the size of the data in a database

  7. Backup Operations Figure 7-1: SQL Statement sp_spaceused executed in a Query window

  8. Backup Operations • Differential database backup • Only backs up data pages that changed since last full database backup was performed • Transaction log backup • Allows transaction logs to be backed up • File backup • Can be used to back up only the data residing on a single file or filegroup of a database

  9. SQL Server 2000 Recovery Models • Recovery model • Model by which you recover the backed up database • Recovery models include: • Simple • Full • Bulk-logged • Each model addresses different needs, including: • Minimizing data loss in the event of a failure • Amount of disk space available to stored backups • Impacting performance of the system

  10. SQL Server 2000 Recovery Models Table 7-1: SQL Server 2000 recovery models

  11. Simple Recovery • Simple recovery model requires the least administration Figure 7-2: Simple recovery model performing full backups everyday

  12. Simple Recovery Figure 7-3: Simple recovery model performing both full and differential backups

  13. Full Recovery and Bulk-Logged Recovery • Full recovery model is regarded as the model of choice to restore a database to an earlier point in time • Full recovery model uses full backups, differential backups and transaction logs to restore a database to a point in time very close to when a system failure occurred

  14. Full Recovery and Bulk-Logged Recovery • Location of the transaction log • It is strongly recommended that the transaction log for a mission-critical database be placed on a separate physical disk than the data • Transaction logs in bulk-logged recovery model • Like full recovery model, bulk-logged provides optimal performance and limited exposure to data loss • Differentiating factor between the two models is how operations are logged

  15. Implementing a Backup Scheme with Transaction Logs Figure 7-4: Full recovery model performing both full and transaction log backups Figure 7-5: Full recovery model performing full, differential and transaction logs

  16. Implementing a Backup Scheme with Transaction Logs Figure 7-6 Full recovery model performing full file and transaction log backups

  17. Configuring the Recovery Model of Databases Figure 7-7: Configuring the database recovery model in Enterprise Manager

  18. Backup Devices • Backup devices • Files on hard drives or other disk storage media used to store backups • Disk devices • Files that reside on hard drives or other storage media on a network • Tape devices • Allow backups to be stored on tapes in a local tape drive

  19. Microsoft Tape Format • Microsoft Tape Format (MTF) • Standard backup format developed by Microsoft to allow backups from multiple sources to be stored on the same media • Media • Largest unit in MTF • Can house multiple backups from both Windows 2000/NT and SQL Server 2000 • Backup set • Unit in MTF that houses a backup from a single source like SQL Server 2000 or Windows 2000/NT

  20. Microsoft Tape Format Figure 7-8: Microsoft Tape Format (MTF) structure

  21. Managing Database Backups • Performing Backups with Enterprise Manager • Enterprise Manager provides intuitive interfaces to simplify process of managing and performing backups Figure 7-9: Creating new backup device with Enterprise Manager

  22. Managing Database Backups Figure 7-10: SQL Server Backup window

  23. Managing Database Backups • In order to back up the database, you must specify a location where the backup will reside Figure 7-11: Select Backup Destination window

  24. Performing Backups with Transact-SQL • Creating backup devices with Transact-SQL • The sp_adddumpdevice system-stored procedure is used to create a new backup device using T-SQL • This procedure must be run from the Master database

  25. Creating Backups with T-SQL • BACKUP T-SQL statement is used with various options to perform full and differential database backups as well as transaction log backups Table 7-2: Common BACKUP DATABASE options

  26. Restoring Databases • Databases can be restored from a backup or set of backups using both Enterprise Manager and T-SQL statements Figure 7-12: Restore database window

  27. Restoring Databases Figure 7-13: Options for performing customized restoration operations

  28. Restoring Databases with Transact-SQL • The RESTORE T-SQL statement is used to recover different backups should the need arise • SQL Server 2000 recovery architecture is very flexible

  29. Database Consistency and the DBCC Statement • System and software errors can cause low-level errors in data and page allocations of a database • DBCC CHECKDB validates consistency of everything in a database • For each table in a database (including its indexes), DBCC CHECKDB checks that: • Indexes and data pages are linked correctly on a hard drive • Indexes are arranged in their proper sort order • All pointers are valid • Data is spread out evenly across pages

  30. Database Consistency and the DBCC Statement • When specified with one of three REPAIR options, DBCC CHECKDB statement repairs errors encountered while performing consistency checks • REPAIR_FAST option causes statement to perform minor repairs that are not time-consuming • REPAIR_REBUILD option performs all of repair activities of REPAIR_FAST option as well as more time-consuming fixes like rebuilding indexes • REPAIR_ALLOW_DATA_LOSS option performs all of the checks of the errors that REPAIR_REBUILD option does, plus fixes allocation and structural problems at the page and row level

  31. Database Maintenance Plans Figure 7-14: Database Maintenance Plan Wizard

  32. Database Maintenance Plans Figure 7-15: Selecting databases

  33. Database Maintenance Plans • Update Database Optimization Information allows various optimization procedures to be configured and scheduled Figure 7-16: Updating data optimization information

  34. Database Maintenance Plans Figure 7-17: Edit Recurring Job Schedule window

  35. Database Maintenance Plans Figure 7-18: Checking database integrity

  36. Database Maintenance Plans Figure 7-19: Specifying the database backup plan

  37. Database Maintenance Plans Figure 7-20: Specifying the backup disk directory

  38. Database Maintenance Plans Figure 7-21: Specifying the transaction log backup plan

  39. Database Maintenance Plans • Reports to Generate screen allows you to specify a directory in which to store a text-based report generated each time the maintenance plan executes Figure 7-22: Configuring database maintenance plan reports

  40. Database Maintenance Plans • Histories can be written to msdb.dbo.sysmaintplan_history table on the local server or a remote server Figure 7-23: Maintenance Plan History screen

  41. Standby Servers and Log Shipping • Warm standby server is a backup computer that is kept in sync with a computer supporting a production environment • They are used to recover the production database in the event of a hardware failure or to offload query processing by providing a read-only database for queries and reports • Log shipping • The process by which transaction logs are backed up from one database, copied to a remote SQL Server 2000 computer and applied to an identical remote database

  42. Standby Servers and Log Shipping • In the log shipping architecture, there are three entities that interact: • A source server that houses a production database whose transaction log backups will be applied to one or more standby database servers • One or more destination servers, which will receive transaction log backups from the source server and apply them to a local database • A monitor server that is responsible for coordinating the log shipping process

  43. Standby Servers and Log Shipping Figure 7-24: Log shipping architecture

  44. Chapter Summary • SQL Server 2000 provides a robust backup and recovery architecture that allows operations to be tailored to meet individual needs of different systems • Transaction log backups can be used to recover a failed database all the way up to a specific time or even a specific transaction • Backups are not the only tool for ensuring the integrity and consistency of data • The DBCC CHECKDB T-SQL statement is used to detect and repair small errors that occur within a database

  45. Chapter Summary • SQL Server 2000 Database Maintenance Plan Wizard provides a time-saving interface for configuring recurring backup and consistency checking operations • Log shipping is a powerful feature of SQL Server 2000 that maintains a warm standby server by automatically copying transaction log backups from a production database to a remote SQL Server 2000 instance and applying each log backup to a database that is kept in sync with the production system

More Related