Vinod KumarTechnology EvangelistMicrosoftwww.ExtremeExperts.com

What We Will Cover

SQL Server Storage Internals

Important SQL Server 2005/2008 Enhancements

Data Recovery Scenarios and Troubleshooting Techniques

Agenda

SQL Server Storage Internals

SQL Server 2005 Enhancements

System Database Recovery

User Database Inaccessible

BACKUP/RESTORE Failures

Database Consistency Errors

SQL Server Storage Internals

Database and File States

Resource Database

Catalog Views and System Base Tables

Allocation Structures

Database Checksum

Fast Recovery

Deferred Transactions

Read-Only Compressed Databases

Storage Internals

BACKUP/RESTORE Enhancements

BACKUPWITH CHECKSUM

Full-text integrated

RESTOREVERIFYONLY

WITH CONTINUE_AFTER_ERROR

Page Level Restore

DBCC CheckDB Enhancements

Data Recovery Scenarios

System Database Recovery

User Database Inaccessible

BACKUP/RESTORE Failures

Database Consistency FailuresRuntime Errors

DBCC CHECKDB Errors

System Database Recovery

Recovering MasterFiles not available

Rebuild using Setup and Restore (you must reapply fixes)

Recovering ModelRebuild using Setup (you must reapply fixes)

Recovering MSDBSystem can still start but SQLAgent will not

instmsdb.sql not an option (at this time) to build clean

System Database Recovery (2)

Recovering Resource DatabaseCopy the correct version of the files

Rebuild using Setup (you must reapply fixes)

Failure to create tempdbServer can start as long as primary files can be created

Operating System ReinstallationSQL Setup can reuse existing system databases

System Database Scenarios

BACKUP/RESTORE Scenarios

DataConsistency Scenarios

Summary

SQL Server 2005 greatly enhances data recovery capabilities

CHECKSUM can help detect problems early

Previous problems may not occur as often (Ex. Deferred transactions, read-retry)

Nothing substitutes a good backup

http://www.microsoft.com/india/msdn

© 2009 Microsoft Corporation. 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.