O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQL Server Backup and Restore

Book Description

In this book, you'll discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool. Capturing backups using SSMS or simple scripts is perfectly fine for one-off backup operations, but any backups that form part of the recovery strategy for any given database must be automated and you'll also want to build in some checks that, for example, alert the responsible DBA immediately if a problem arises. The tool of choice in this book for backup automation is Red Gate SQL Backup. Building your own automated solution will take a lot of work, but we do offer some advice on possible options, such as PowerShell scripting, T-SQL scripts and SQL Server Agent jobs.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Table of Contents
  5. About the author
  6. Introduction
    1. Software Requirements and Code Examples
  7. Chapter 1: Basics of Backup and Restore
    1. Components of a SQL Server Database
    2. SQL Server Backup Categories and Types
    3. Recovery Models
    4. Restoring Databases
    5. Summary
  8. Chapter 2: Planning, Storage and Documentation
    1. Backup Storage
    2. Backup Tools
    3. Backup and Restore Planning
    4. Backup Verification and Test Restores
    5. Documenting Critical Backup Information
    6. Summary
  9. Chapter 3: Full Database Backups
    1. What is a Full Database Backup?
    2. Why Take Full Backups?
    3. Full Backups in the Backup and Restore SLA
    4. Preparing for Full Backups
    5. Taking Full Backups
    6. Native Backup Compression
    7. Verifying Backups
    8. Building a Reusable and Schedulable Backup Script
    9. Summary
  10. Chapter 4: Restoring From Full Backup
    1. Full Restores in the Backup and Restore SLA
    2. Possible Issues with Full Database Restores
    3. Performing Full Restores
    4. Forcing Restore Failures for Fun
    5. Considerations When Restoring to a Different Location
    6. Restoring System Databases
    7. Summary
  11. Chapter 5: Log Backups
    1. A Brief Peek Inside a Transaction Log
    2. Three uses for transaction log backups
    3. Log Backups in the Backup and Restore SLA
    4. Preparing for Log Backups
    5. Taking Log Backups
    6. Forcing Log Backup Failures for Fun
    7. Troubleshooting Log Issues
    8. Summary
  12. Chapter 6: Log Restores
    1. Log Restores in the SLA
    2. Possible Issues with Log Restores
    3. Performing Log Restores
    4. Forcing Restore Failures for Fun
    5. Summary
  13. Chapter 7: Differential Backup and Restore
    1. Differential Backups, Overview
    2. Preparing for Differential Backups
    3. Taking Differential Backups
    4. Performing Differential Backup Restores
    5. Forcing Failures for Fun
    6. Summary
  14. Chapter 8: Database Backup and Restore with SQL Backup Pro
    1. Preparing for Backups
    2. Full Backups
    3. Log Backups
    4. Differential Backups
    5. Building a reusable and schedulable backup script
    6. Restoring Database Backups with SQL Backup Pro
    7. Verifying Backups
    8. Backup Optimization
    9. Summary
  15. Chapter 9: File and Filegroup Backup and Restore
    1. Advantages of File Backup and Restore
    2. Common Filegroup Architectures
    3. File Backup
    4. File Restore
    5. Common Issues with File Backup and Restore
    6. File Backup and Restore SLA
    7. Forcing Failures for Fun
    8. Summary
  16. Chapter 10: Partial Backup and Restore
    1. Why Partial Backups?
    2. Performing Partial Database Backups
    3. Performing Partial Database Restores
    4. SQL Backup Pro Partial Backup and Restore
    5. Possible Issues with Partial Backup and Restore
    6. Partial Backups and Restores in the SLA
    7. Forcing Failures for Fun
    8. Summary
  17. Appendix A: SQL Backup Pro Installation and Configuration
    1. SQL Backup Pro GUI Installation
    2. SQL Backup Pro Services Installation
    3. SQL Backup Pro Configuration