Uploaded byVinod Kumar
Backup beyond just a strategy with SQL Server
This document discusses strategies for effective SQL Server backups. It covers the phases of restore including file creation, data/log copying, redo, and undo. It discusses ways to optimize restore speed such as skipping zero initialization, hardware configuration tuning, and backup frequency. It also covers piecemeal restores of files/filegroups to maintain partial database availability and techniques for simple recovery model databases.
More Related Content
Similar to Backup beyond just a strategy with SQL Server
Backup beyond just a strategy with SQL Server
- 1. Backup beyond justa strategy with SQL ServerVinod KumarTechnology EvangelistMicrosoft Corporationwww.ExtremeExperts.comhttp://blogs.sqlxml.org/vinodkumar
- 2. Session Objectives andTakeawaysSession ObjectivesKnow some of the basics of SQL ServerWhat makes an effective Strategy for backupBoth from Technology EnhancementsActual implementationsTakeawaysThese as much as they look theoretical, We will view some of the fundamentals during the demo to make you realize how important backups are !!!
- 3. Targeted RolesDatabase AdministrationDatabase Development Database Operations and Support Database Solutions Architect
- 4. File Creation/InitializationCreation ofdatabase filesFiles being restored are created and initialized.This step is NOT performed if file already exists.Do not drop database prior to restoring it.Time requirementSQL Server 2000Write throughput of data devices (disks, RAID controllers, etc.)Same time as CREATE DATABASE or ALTER DATABASE ADD FILESQL Server 2005+ – Zero-initialization SKIPPED
- 5. Data/Transaction Log CopyFilecontaining data or transaction log is copied from backup devices to destinationLength of this phase:Amount of data or log to be copiedSlowest component of the I/O system (disk device, backup device, controller, PCI bus, network)System tuned for maximum copy speed is limited by memory bus bandwidth—or before that, number of PCI buses or slots supported by systemCPU usage should be insignificant
- 6. RedoRedo (or roll-forward)occurs after the log has been restored.Reads the transaction log.Reapplies changes recorded in log to data pages so all pages are consistent with log.Length of this phase is determined by…Amount of log to redo (affected by type and frequency of backups), andSystem performance.Planning: How fast is redo relative to production workload?
- 7. UndoUndo is alsocalled “roll-back” or “recovery.”Begins after redo reaches it target point, often the point of failure.Changes that were applied by active, uncommitted transactions at the target point are undone.Length of this phase:The amount of data modified by the active transactionsPlanning: Very hard – depends on how transactions have been written and/or if long running transactions exist…
- 8. Phases of RestoreFileCreation/Initialization Same as CREATE DATABASEMinimized by Skipping zero-initializationData and/or Transaction Log Copy Based on Media type and DB Size Minimized by Optimizing HW Configuration and ThroughputRedo/Roll-forward Log Type/Frequency of BackupsMinimized by SQL Server Transaction Log InformationUndo Uncommitted Transactions Impossible to predictDatabase Accessible upon REDO completion!
- 9. Instant File InitializationSkipdata file zeroingCreate VLDB,Add or grow file,Initialize for restore,…in seconds!SecurityPrior disk contents not overwritten until usedExtra care if SQL Server shut down or DB detached Secure by defaultPermission grantable only by Windows administrator
- 10. Online RestoreSQL Server2000One damaged page/device/file ENTIRE database marked suspect, taken “offline”Database is not available during restoreSQL Server 2005+ onwardsDatabase remains onlineOnly data being restored is unavailableFile/filegroup restore
- 11. Piecemeal ManagementFine GrainedOperationsOperations affect the minimum amount of dataPagesFilegroupsPartitionsData not involved remainsAvailablePerformant
- 12. FilegroupsBasic unit ofavailabilityEarlier Versions of SQL ServerEntire databaseSQL Server 2005+Database can be online, yet not all filegroups are available
- 13. AvailablePrimaryFilegroupDatabaseFilegroup AFilegroup BExamplePiecemealrestore for disaster recoveryOnline restore of filegroups in priority orderBackupsPrimaryLogFilegroup AFilegroup B
- 14. Piecemeal Backup-RestorePartial databaseavailability during restoreBased on file/filegroup backupWorks with all recovery modelsFull, Simple, Bulk-LoggedSupported only for databases with multiple filegroupsFilegroups must be recovered to consistent point
- 15. Short Restore SequenceForRead-Only Data Log backups need not be restored when…File/filegroup is unchanged since backupFile has been rolled forward to where it is read-only Useful for read-only tables or partitionsSet filegroup read-onlyRecommended for FULL and BULK-LOGGED databasesPrevents inadvertent changePrevents changes by background tasksRequired for SIMPLE model databasesLog backup for roll forward not available
- 16. Primary FilegroupFilegroup ARESTORE DATABASE D… FILEGROUP=‘B’AvailableRestore Damaged FilegroupFull model databaseLog BackupsPrimary BackupDatabaseFilegroup A BackupFilegroup BBackupFilegroup BRead - OnlyFilegroup CBackupFilegroup CRead - Only
- 17. Recovery Models -GlanceFull No work lossSupports recovery to any point-in-timeSimpleSimplest backup/restore strategySimplifies log managementNo log backupsGreatest work loss possibleNo point-in-time recoveryNo access to tail Bulk_LoggedHigh performance bulk operationsMinimal log space for bulk operationsSome work loss exposureMany large data warehouses fit this model
- 18. Partial BackupsTwo newtypes of backupPartial DatabaseSimilar to a full database backup, but contains only the read-write filegroupsPartial DifferentialSimilar to a full database differential, but contains only the read-write filegroupsAdded to allow piecemeal restore of simple model databasesAlso work with full and bulk-logged models
- 19. Piecemeal Backup-RestoreSimple ModelDatabasesRequired backupsPartial database backupFile or filegroup backups of read-only filegroupsMust be consistent with partialOptional backupsPartial differentialFile/filegroup differentialIf filegroup normally read-only was changed, then made read-only again
- 20. BACKUP DATABASED… WITH PARTIALPrimary FilegroupFilegroup ABACKUP DATABASE D… FILEGROUP=‘B’BACKUP DATABASE D… FILEGROUP=‘C’Piecemeal BackupSimple model databaseDatabasePartial BackupFilegroup BBackupFilegroup BRead - OnlyFilegroup CBackupFilegroup CRead - Only
- 21. RESTORE DATABASED… WITH PARTIALPrimary FilegroupFilegroup ARESTORE DATABASE D… FILEGROUP=‘B’Filegroup BRead - OnlyRESTORE DATABASE D… FILEGROUP=‘C’Filegroup CAvailableRead - OnlyPiecemeal RestoreSimple model databaseDatabasePartial BackupFilegroup BBackupFilegroup CBackup
- 22. Primary FilegroupFilegroup ARESTORE DATABASE D… FILEGROUP=‘B’AvailableRestore Damaged FilegroupSimple model databaseDatabasePartial BackupFilegroup BBackupFilegroup BRead - OnlyFilegroup CBackupFilegroup CRead - Only
- 23. UnavailableRESTORE DATABASED… WITH PARTIALAvailableRestore Damaged PrimarySimple model databaseDatabasePartial BackupPrimary FilegroupFilegroup AFilegroup BBackupFilegroup BDatabase Primary and A may be taken back in time without restoring filegroups B and C if B and C are consistentRead - OnlyFilegroup CBackupFilegroup CRead - Only
- 24. Damaged Page TrackingAnd RestoreDamaged pages encountered on read Torn page or checksum errorBad Page IDShort readDatabase remains availableTransaction rolls backBUT error during rollback forces database restartDamaged pages Automatically tracked in a tableMay be restored and recovered onlineWorks for databases with a single filegroup
- 25. Page Restore VersusFile RestoreConsider page restore when…You have a limited number of damaged pagesYou must maintain maximum availabilityConsider file restore when…A device is failingYou have large numbers of damaged pagesYou need to relocate data to another deviceYou can tolerate the affected filegroupbeing unavailable
- 26. Data Page VerificationTypesTornpage detection – 7.0 and 2000Page checksum – NEW!Page checksumDetect disk I/O errors not reported by the hardware or operating systemWhen the checksum is verifiedRetries eliminate transients If verification fails,A unique error is raised Connection is terminatedPage is tracked
- 27. Backup VerificationDetect errorsunreported by the backup devices or operating systemBackup checksums are optionally generatedVerified byRESTORERESTORE VERIFYONLYBackup optionally validates page checksums if these are presentOptionally continue past page checksum errors to complete the backup
- 28. Continue Past RestoreErrorsWhy?A damaged backup may be the only one availableContinue past errors encountered byRESTORERESTORE VERIFYONLYAllows the restore sequence to continue as far as possible…Affected file groups are left in suspect state
- 29. Backup/RestoreMirrored Media SetsRedundantbackup media helps guarantee successful restoreExtra copies forArchivalDisaster recovery
- 30.
- 31. 31SummaryBackups are themost important step in restoring your DB in event of failureMaybe your first and last resortMust understand the effects of using themKnow ALL the options in handling disasterAll the Best !!!
- 32.
- 33. © 2007 MicrosoftCorporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.