SQL Server Backup and Restore

391 Pages·2012·4.779 MB·English

by  Shawn McGehee

The Red Gate Guide
SQL Server
Backup and Restore
Shawn McGehee
ISBN: 978-1-906434-74-8
SQL Server Backup
and Restore
By Shawn McGehee
First published by Simple Talk Publishing April 2012
Copyright April 2012
ISBN 978-1-906434-85-4
The right of Shawn McGehee to be identified as the author of this work has been asserted by him in
accordance with the Copyright, Designs and Patents Act 1988.
All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval
system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or
otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act in
relation to this publication may be liable to criminal prosecution and civil claims for damages.
This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent,
re-sold, hired out, or otherwise circulated without the publisher's prior consent in any form other than
that in which it is published and without a similar condition including this condition being imposed on the
subsequent publisher.
Technical Review by Eric Wisdahl
Cover Image by Andy Martin
Edited by Tony Davis
Typeset & Designed by Peter Woodhouse & Gower Associates
Table of Contents
Introduction 12
___________________________________________
Software Requirements and Code Examples 18
________________________________
Chapter 1: Basics of Backup and Restore 19
___________________
Components of a SQL Server Database 20
___________________________________
Data files 20
________________________________________________________
Filegroups 22
_________________________________________________________
Transaction log 24
____________________________________________________
SQL Server Backup Categories and Types 28
__________________________________
SQL Server database backups 29
_________________________________________
SQL Server transaction log backups 32
____________________________________
File backups 36
_______________________________________________________
Recovery Models 39
______________________________________________________
Simple 41
____________________________________________________________
Full 43
______________________________________________________________
Bulk Logged 44
______________________________________________________
Restoring Databases 46
___________________________________________________
Restoring system databases 47
___________________________________________
Restoring single pages from backup 48
____________________________________
Summary 49
___________________________________________________________
Chapter 2: Planning, Storage and Documentation 50
___________
Backup Storage 50
________________________________________________________
Local disk (DAS or SAN) 52
_____________________________________________
Network device 58
____________________________________________________
Tape 59
_____________________________________________________________
Backup Tools 60
________________________________________________________
Maintenance plan backups 61
___________________________________________
Custom backup scripts 62
______________________________________________
Third-party tools 63
___________________________________________________
Backup and Restore Planning 64
___________________________________________
Backup requirements 65
_______________________________________________
Restore requirements 68
______________________________________________
An SLA template 69
__________________________________________________
Example restore requirements and backup schemes 71
_______________________
Backup scheduling 73
__________________________________________________
Backup Verification and Test Restores 75
_____________________________________
Back up WITH CHECKSUM 76
_________________________________________
Verifying restores 77
___________________________________________________
DBCC CHECKDB 77
__________________________________________________
Documenting Critical Backup Information 78
_________________________________
Summary 83
____________________________________________________________
Chapter 3: Full Database Backups 84
_______________________
What is a Full Database Backup? 84
_________________________________________
Why Take Full Backups? 85
________________________________________________
Full Backups in the Backup and Restore SLA 86
_______________________________
Preparing for Full Backups 87
______________________________________________
Choosing the recovery model 88
_________________________________________
Database creation 88
__________________________________________________
Creating and populating the tables 94
____________________________________
Taking Full Backups 96
___________________________________________________
Native SSMS GUI method 97
____________________________________________
Native T-SQL method 106
______________________________________________
Native Backup Compression 111
____________________________________________
Verifying Backups 113
____________________________________________________
Building a Reusable and Schedulable Backup Script 114
_________________________
Summary 115
___________________________________________________________
Chapter 4: Restoring From Full Backup 116
___________________
Full Restores in the Backup and Restore SLA 116
______________________________
Possible Issues with Full Database Restores 117
________________________________
Large data volumes 118
________________________________________________
Restoring databases containing sensitive data 118
___________________________
Too much permission 120
______________________________________________
Performing Full Restores 122
_______________________________________________
Native SSMS GUI full backup restore 122
__________________________________
Native T-SQL full restore 129
___________________________________________
Forcing Restore Failures for Fun 133
_________________________________________
Considerations When Restoring to a Different Location 136
_____________________
Restoring System Databases 137
____________________________________________
Restoring the msdb database 138
________________________________________
Restoring the master database 140
_______________________________________
Summary 143
___________________________________________________________
Chapter 5: Log Backups 144
________________________________
A Brief Peek Inside a Transaction Log 145
____________________________________
Three uses for transaction log backups 148
___________________________________
Performing database restores 149
________________________________________
Large database migrations 150
___________________________________________
Log shipping 151
_____________________________________________________
Log Backups in the Backup and Restore SLA 152
_______________________________
Preparing for Log Backups 153
_____________________________________________
Choosing the recovery model 154
________________________________________
Creating the database 155
______________________________________________
Creating and populating tables 157
_______________________________________
Taking a base full database backup 159
____________________________________
Taking Log Backups 161
___________________________________________________
The GUI way: native SSMS log backups 161
________________________________
T-SQL log backups 166
_________________________________________________
Forcing Log Backup Failures for Fun 170
_____________________________________
Troubleshooting Log Issues 172
____________________________________________
Failure to take log backups 173
__________________________________________
Other factors preventing log truncation 174
_______________________________
Excessive logging activity 175
___________________________________________
Handling the 9002 Transaction Log Full error 176
__________________________
Log fragmentation 177
_________________________________________________
Summary 181
___________________________________________________________
Chapter 6: Log Restores 182
________________________________
Log Restores in the SLA 182
________________________________________________
Possible Issues with Log Restores 183
________________________________________
Missing or corrupt log backup 183
_______________________________________
Missing or corrupt full backup 184
_______________________________________
Minimally logged operations 184
_________________________________________
Performing Log Restores 187
_______________________________________________
GUI-based log restore 188
______________________________________________
T-SQL point-in-time restores 194
________________________________________
Possible difficulties with point-in-time restores 198
__________________________
Forcing Restore Failures for Fun 200
________________________________________
Summary 204
__________________________________________________________
Chapter 7: Differential Backup and Restore 205
______________
Differential Backups, Overview 206
________________________________________
Advantages of differential backups 207
____________________________________
Differential backup strategies 208
_______________________________________
Possible issues with differential backups 212
_______________________________
Differentials in the backup and restore SLA 215
_____________________________
Preparing for Differential Backups 216
_______________________________________
Recovery model 216
___________________________________________________
Sample database and tables plus initial data load 217
_________________________
Base backup 218
______________________________________________________
Taking Differential Backups 218
____________________________________________
Native GUI differential backup 219
_______________________________________
Native T-SQL differential backup 221
_____________________________________
Compressed differential backups 223
_____________________________________
Performing Differential Backup Restores 225
__________________________________
Native GUI differential restore 225
_______________________________________
Native T-SQL differential restore 227
_____________________________________
Restoring compressed differential backups 230
_____________________________
Forcing Failures for Fun 231
________________________________________________
Missing the base 231
__________________________________________________
Running to the wrong base 232
__________________________________________
Recovered, already 235
_________________________________________________
Summary 237
___________________________________________________________
Chapter 8: Database Backup and Restore with SQL Backup Pro 238

Preparing for Backups 238
_________________________________________________
Full Backups 241
_________________________________________________________
SQL Backup Pro full backup GUI method 241
______________________________
SQL Backup Pro full backup using T-SQL 253
______________________________
Log Backups 256
_________________________________________________________
Preparing for log backups 256
___________________________________________
SQL Backup Pro log backups 258
________________________________________
Differential Backups 261
__________________________________________________
Building a reusable and schedulable backup script 263
__________________________
Restoring Database Backups with SQL Backup Pro 267
_________________________
Preparing for restore 267
_______________________________________________
SQL Backup Pro GUI restore to the end of a log backup 269
___________________
SQL Backup Pro T-SQL complete restore 277
______________________________
SQL Backup Pro point-in-time restore to standby 279
________________________
Restore metrics: native vs. SQL Backup Pro 289
_____________________________
Verifying Backups 291
____________________________________________________
Backup Optimization 292
_________________________________________________
Summary 294
___________________________________________________________
Chapter 9: File and Filegroup Backup and Restore 295
_________
Advantages of File Backup and Restore 296
__________________________________
Common Filegroup Architectures 298
_______________________________________
File Backup 303
__________________________________________________________
Preparing for file backups 306
___________________________________________
SSMS native full file backups 309
________________________________________
Native T-SQL file differential backup 310
__________________________________
SQL Backup Pro file backups 314
________________________________________
File Restore 318
__________________________________________________________
Performing a complete restore (native T-SQL) 321
___________________________
Restoring to a point in time (native T-SQL) 326
_____________________________
Restoring after loss of a secondary data file 328
_____________________________
Quick recovery using online piecemeal restore 335
__________________________
Common Issues with File Backup and Restore 340
_____________________________
File Backup and Restore SLA 341
____________________________________________
Forcing Failures for Fun 343
________________________________________________
Summary 346
___________________________________________________________
Chapter 10: Partial Backup and Restore 348
_________________
Why Partial Backups? 349
_________________________________________________
Performing Partial Database Backups 350
_____________________________________
Preparing for partial backups 351
________________________________________
Partial database backup using T-SQL 354
__________________________________
Differential partial backup using T-SQL 355
________________________________
Performing Partial Database Restores 357
____________________________________
Restoring a full partial backup 357
_______________________________________
Restoring a differential partial backup 359
_________________________________
Special case partial backup restore 360
____________________________________
SQL Backup Pro Partial Backup and Restore 362
_______________________________
Possible Issues with Partial Backup and Restore 364
____________________________
Partial Backups and Restores in the SLA 365
__________________________________
Forcing Failures for Fun 366
________________________________________________
Summary 367
___________________________________________________________
Appendix A: SQL Backup Pro Installation and Configuration 368

SQL Backup Pro GUI Installation 368
________________________________________
SQL Backup Pro Services Installation 370
____________________________________
SQL Backup Pro Configuration 376
_________________________________________
File management 376
__________________________________________________
Email settings 378
_____________________________________________________