Praveen Srivatsa Director AstrhaSoft Consulting blogsasthrasoftcompraveens praveensasthranet Whats NewImproved Mirrored Backups Partial BackupsRestores amp Piecemeal Restores ID: 389027
Download Presentation The PPT/PDF document "Backup and Restore" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Backup and Restore
Praveen
Srivatsa
Director
|
AstrhaSoft
Consulting
blogs.asthrasoft.com/praveens
|
praveens@asthra.netSlide2
What’s New/Improved
Mirrored Backups
Partial Backups/Restores & Piecemeal Restores
Improved reliability with Backup Checksums
Online Restore
Fast Recovery
Improved RESTORE VERIFYONLY
Page RestoresSlide3
Simple Recovery Model
Backup supports the following backup types:
Full Backup
Full Backup
Partial Backup (
*
New)
File Backup
Differential Backup
Full Differential Backup
Partial Differential Backup
File Differential Backup Slide4
Full Recovery Model
In Addition to Full and Differential Backups, it supports the following backup types:
Transaction log Backups
Pure Log Backup
Contains only transaction logs without and bulk changes
Bulk Log Backup
Includes log and, also, data pages changed by bulk operations.
Tail Log Backup
Taken from a possibly damaged database to capture the log that has not yet been backed upSlide5
Enhanced Error Detection
In SQL Server 2000 RESTORE VERIFYONLY does not guarantee that the backup is good
Data may be corrupt
In SQL Server 2005 RESTORE VERIFYONLY checks everything
Ensures that the data is correctSlide6
Database Checksums
SQL Server 2000 had TornPageDetection to detect incomplete I/O Operations by power failures
SQL Server 2005 adds checksums to data pages
Header of every page contains a checksum value
When reading page, it re-computes checksum and compares with checksum stored
Returns error (824) if difference found
Detects errors not reported by I/O SubsystemSlide7
Backup Checksums
Detect errors introduced by backup hardware but not reported by hardware or operating system
Backup media error detection
Backup devices do not always detect errors
Works with
RESTORE
RESTORE VERIFYONLY
Restore also checks page checksums, if present
Disk error detection on data pages prior to backup
Can continue past errors if desiredSlide8
Restores – New Features
SQL Server 2000
Database unavailable while restoring
SQL Server 2005
Online Restores
Partial Restores
Skips roll forward of logs for read-only data filesSlide9
Online Restores
Supported in Enterprise, and Developer editions
For Databases containing multiple filegroups
Only the secondary filegroup being restored is unavailable
When restoring filegroups, all filegroups must be restored to bring the database to a consistent stateSlide10
Piecemeal Restores
The goal is to restore and recover a database in stages, by filegroup(s)
The primary filegroup must be restored in the first stage
Database is brought online
Critical Data is online
Other filegroups not restored are marked offline
Remaining files can be restored later
Transaction Logs are not applied to read only filegroupsSlide11
Large multi-terabyte databases and many databases per instance
Very Large Backups
Use multiple backup devices simultaneously
Parallel I/O
Parallel task execution
Up to 64 devices supported
Use snapshot backupsSlide12
Large multi-terabyte databases and many databases per instance
Disk Backup Performance
Scales linearly with number of disks
RAID 5 - poor write performance
Separate drive usually best
Sometimes can use multiple backups per logical drive (test)
Compression
Hardware
Application – Third-party toolsSlide13
Database Maintenance Wizard
The Database maintenance wizard has been enhanced to support additional features. Maintenance
Tasks
Included
Check database integrity
Shrink database
Defragment Indexes
Re-index
Update statistics
History cleanup
Launch SQL Server Agent Job
Backup database (Full)
Backup database (Differential)
Backup database (Transaction Log)Slide14
Feedback / QnA
Your Feedback is Important!
Please take a few moments to fill out our online feedback form
For detailed feedback, use the form at
http://www.connectwithlife.co.in/vtd/helpdesk.aspx
Or email us at
vtd@microsoft.com
Use the Question Manager on LiveMeeting to ask your questions now!Slide15