Backup and Restore

1. Backup and Restore Praveen Srivatsa Director|AstrhaSoft Consulting blogs.asthrasoft.com/praveens|praveens@asthra.net

2. What’s New/Improved • Mirrored Backups • Partial Backups/Restores & Piecemeal Restores • Improved reliability with Backup Checksums • Online Restore • Fast Recovery • Improved RESTORE VERIFYONLY • Page Restores

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

4. 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 up

5. 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 correct

6. 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 Subsystem

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

8. 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 files

9. 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 state

10. 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 filegroups

11. 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 backups

12. 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 tools

13. 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)

14. 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!