SQL Server Backup and Recovery : Tools and Techniques
The in-depth, start-to-finish guide to Microsoft "RM" SQL Server backup for every DBA!-- Comprehensive coverage of SQL Server 2000, SQL Server 7.x, and Windows XP backup and recovery tools.-- Helps DBAs maximize their efficiency with extensive sample code and scripts, plus detailed coverage of third-party tools.-- An essential resource! SQL Server backup is complex, Microsoft "RM"'s documentation is weak, and no other book exists.Written by a Microsoft insider who specializes in SQL Server enterprise deployments, SQL Server Backup and Recovery is the first comprehensive guide to protecting and restoring SQL Server databases. Organized to serve as both a hands-on tutorial and a long-term reference, it contains in-depth coverage of both SQL Server 2000 and 7.x -- as well as up-to-the-minute guidance on using Windows XP backup and recovery tools with SQL Server databases. Coverage includes: SQL Server architecture, fundamental backup and recovery principles, tools and scenarios, and SQL Server diagnostic tools. The book includes extensive sample code and scripts designed to help DBAs streamline and automate backup and recovery; as well as detailed information on the leading third party tools for SQL Server backup and recovery. All examples and screen shots are shown utilizing Windows XP, helping to extend the book's value for years to come. For every Microsoft "RM" SQL Server database administrator, and for every IT manager and professional responsible for protecting data stored on Microsoft "RM" SQL Server databases.
- Paperback | 320 pages
- 176.78 x 232.66 x 22.35mm | 603.27g
- 03 Dec 2001
- Pearson Education (US)
- Prentice Hall
- Upper Saddle River, United States
- w. figs.
About Frank McBath
FRANK McBATH is an SQL Server specialist in Microsoft's Enterprise Applications Practice specializing in architecting and implementing large database installations. Before joining Microsoft, McBath worked for several Fortune 100 firms and consulting houses.
Table of contents
(NOTE: Each chapter ends with summary.)Introduction. Who This Book Is For. How This Book Is Organized. What You'll Need. Acknowledgements. 1. Planning, Architecture Overview, and Practical Experience. Planning Your Backup and Recovery. The Role of Backups. You're Only as Good as Your Last "Good" Backup. Types of Backups. How BACKUP Works. Databases You Should Back Up. Database Maintenance Plans. Backup Security. How RESTORE Works. Recovery Models. How Recovery Models Work. Setting Recovery Models. Changing Recovery Models. Limitations on Recovery Models. Recovery Model Backup Compatibility Matrix. Practical Experience.2. Backing Up Data in SQL Server. SQL Server Tools. Backup Devices. Using Enterprise Manager to Define a Backup Device. Using Transact SQL to Define a Backup Device. Adding a Disk Backup Device with sp_addumpdevice. Adding a Tape Backup Device with sp_addumpdevice. Viewing Backup Devices. Dropping Backup Devices. Introduction to Backup Methods. Using the EM to Run a Backup. Using the Command Line to Back Up a Database. Backing Up a Database to Disk. Backing Up a Database to Tape. Making a Differential Database Backup. Backing Up Filegroups. Transaction Logs. Transaction Log Backup. Recovering the Tail of a Transaction Log. Backing Up a Whole SQL Server. Offline Backup. Backing Up an Entire Online Server. Backing Up a Table. Select Into. bcp. Running bcp. Data-Loading Strategies and bcp. DTS. DTS Performance Considerations. Flat File Backups. Performing Cold Backups of an NT File. Filesystem Backup: Architecture and Performance Considerations. Using sp_detach_db to Back Up. Scheduling Backups. Using SQL Server Agent with Your Backups. Using AT. Integrating E-Mail into Your Backup. Backup Performance and Operational Considerations. Striping with Tape Systems. Compression: Tape, OS, and Databases. Long-Term Storage. Compressed Volumes and SQL Server: Databases, Transaction Logs, and Backups. Monitoring the Completion of a Backup. Monitoring the Performance of a Backup. Tuning Up BACKUP.3. Restoring Data to SQL Server. Appended Backups. Restoring a Database from Disk. Restoring a Database from Tape Using the Command Line. Large Restore from Tape. How to Restore a Database Using Transaction Logs. Assumptions. Process. Example. Recovery with Differential Backups. Assumptions. Process. Example. Restoring from a Differential Backup with Transaction Logs from a Tape Device. Restoring to a Point in Literal Time from Tape. Full Backup with Multiple Transaction Logs. Bringing It All Together: Moving Data Centers Using Standby Servers. Source Database Server. Target Standby Database Server. Other Uses for a Standby Server. Moving Database Files on a Restore. Restoring to a Marked Transaction. Results of Script. Restoring Files and Filegroups. Example. Recovering msdb. Example. Restoring Usernames and Logins. Recovering a Database with a Transaction Log Only. Example.4. Recovering the master Database. Rebuilding and Recovering the master Database. Scenario. Restoring the master Database. Potential Problems in Rebuilding and Restoring the master Database. Recovering Other Databases after master Has Been Rebuilt. Using the RESTORE Command. Using the sp_attach_db Method. Notes on Logins and Users after a master Rebuild. Program to Generate Logins from the User Database. Remapping Orphaned Users. Recovering msdb. Process. Script.5. Data Recovery Tools and Techniques. Establishing a Game Plan. Sizing Up the Situation. Common Diagnostic Tools. DBCC. SQLDIAG.EXE. Introduction to DBCC PAGE. DBCC PAGE Syntax. Finding and Repairing Corrupt Data with DBCC CHECKDB. Using Indexes to Recover Data. Case One. Case Two. Corruption That's Hard to Detect. Fixing Corrupt Nonclustered Indexes. Backing Up and Restoring Corruption. Retrieving Data Using DBCC PAGE. Using sp_attach_single_file_db to Recover an MDF File.6. Log Shipping. Pros and Cons of Some High-Availability Solutions. Simple Log Shipping. Preliminary Steps. Creating a Maintenance Plan for Log Shipping. Explanation of the Files. SQL Agent Jobs. Maintenance Plans. Testing to See if Log Shipping is Really Working. Removing Log Shipping. Two Secondary Servers. Creating a Maintenance Plan for Log Shipping with Two Secondary Servers. Primary/Secondary to Primary/Secondary. Failing Over: Network Load Balancing Router. No Recovery Mode and Standby Mode. Log Shipping Monitor. Viewing the Log Shipping Monitor. Role Change to the Standby Log Shipping Server. Failing Over if Both Servers Are Accessible. Logins and Users During Log Shipping. Failing Over in an Emergency: Only the Destination Server Is Up. Re-Establishing Log Shipping After Recovering the Primary. Good Ideas Before Failing Over. Potential Problems During Log Shipping.Appendix A: An Efficient and Flexible Method for Archiving a Data Base. Appendix B: ER Diagrams. Appendix C: Acronyms and Abbreviations. Bibliography.Index.