SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach

SQL Server 2008 Transact-SQL Recipes......Page 1
Contents at a Glance......Page 6
Contents......Page 9
About the Author......Page 27
About the Technical Reviewer......Page 29
Acknowledgments......Page 31
Introduction......Page 33
The Basic SELECT Statement......Page 35
How It Works......Page 36
Selective Querying Using a Basic WHERE Clause......Page 37
Combining Search Conditions......Page 38
How It Works......Page 39
Keeping Your WHERE Clause Unambiguous......Page 40
Using Operators and Expressions......Page 41
Using Comparisons......Page 43
How It Works......Page 44
Using Wildcards with LIKE......Page 45
Declaring and Assigning Values to Variables......Page 46
How It Works......Page 47
How It Works......Page 48
Using GROUP BY ALL......Page 49
Selectively Querying Grouped Data Using HAVING......Page 50
Using the ORDER BY Clause......Page 51
How It Works......Page 52
Using the TOP Keyword with Ordered Results......Page 53
How It Works......Page 54
Using DISTINCT to Remove Duplicate Values......Page 55
Using Column Aliases......Page 56
Using SELECT to Create a Script......Page 57
Performing String Concatenation......Page 58
How It Works......Page 59
Using the INTO Clause......Page 60
Using Subqueries to Check for Matches......Page 61
Querying from More Than One Data Source......Page 62
How It Works......Page 63
Using OUTER Joins......Page 64
Using CROSS Joins......Page 65
How It Works......Page 66
Combining Result Sets with UNION......Page 67
How It Works......Page 68
Using CROSS APPLY......Page 69
How It Works......Page 70
Using OUTER APPLY......Page 71
Using the TABLESAMPLE to Return Random Rows......Page 72
Using PIVOT to Convert Single Column Values into Multiple Columns and Aggregate Data......Page 73
How It Works......Page 75
Normalizing Data with UNPIVOT......Page 76
How It Works......Page 77
Returning Distinct or Matching Rows Using EXCEPT and INTERSECT......Page 78
How It Works......Page 79
Summarizing Data Using CUBE......Page 80
Summarizing Data Using ROLLUP......Page 82
Creating Custom Summaries Using Grouping Sets......Page 83
Revealing Rows Generated by GROUPING......Page 85
How It Works......Page 86
Advanced Group-Level Identification with GROUPING_ID......Page 87
How It Works......Page 89
Using a Non-Recursive Common Table Expression......Page 90
How It Works......Page 92
Using a Recursive Common Table Expression......Page 93
How It Works......Page 94
INSERT......Page 97
How It Works......Page 98
Inserting a Row Using Default Values......Page 99
Explicitly Inserting a Value into an IDENTITY Column......Page 100
Inserting a Row into a Table with a uniqueidentifier Column......Page 101
Inserting Rows Using an INSERT...SELECT Statement......Page 102
How It Works......Page 103
Inserting Data from a Stored Procedure Call......Page 104
Inserting Multiple Rows with VALUES......Page 105
Using VALUES As a Table Source......Page 106
UPDATE......Page 107
How It Works......Page 108
How It Works......Page 109
Updating Large Value Data Type Columns......Page 110
How It Works......Page 111
Inserting or Updating an Image File Using OPENROWSET and BULK......Page 112
How It Works......Page 113
Storing Unstructured Data on the File System While Maintaining SQL Server Transactional Control......Page 114
How It Works......Page 117
Assigning and Modifying Database Values “in Place”......Page 118
Deleting Rows......Page 120
How It Works......Page 121
How It Works......Page 122
Chunking Data Modifications with TOP......Page 123
Executing INSERTs, UPDATEs, and DELETEs in a Single Statement......Page 124
How It Works......Page 126
Returning Rows Affected by a Data Modification Statement......Page 127
How It Works......Page 129
Asynchronously Capturing Table Data Modifications......Page 130
Querying All Changes from CDC Tables......Page 133
How It Works......Page 136
Querying Net Changes from CDC Tables......Page 137
Translating the CDC Update Mask......Page 138
Working with LSN Boundaries......Page 139
How It Works......Page 140
Tracking Net Data Changes with Minimal Disk Overhead......Page 141
How It Works......Page 147
Transaction Control......Page 149
Using Explicit Transactions......Page 151
How It Works......Page 152
Displaying the Oldest Active Transaction with DBCC OPENTRAN......Page 153
Querying Transaction Information by Session......Page 154
Locking......Page 156
Viewing Lock Activity......Page 158
How It Works......Page 159
Controlling a Table’s Lock Escalation Behavior......Page 160
How It Works......Page 161
Transaction, Locking, and Concurrency......Page 162
Configuring a Session’s Transaction Locking Behavior......Page 163
How It Works......Page 166
Identifying and Resolving Blocking Issues......Page 168
Configuring How Long a Statement Will Wait for a Lock to Be Released......Page 170
Deadlocking......Page 171
Identifying Deadlocks with a Trace Flag......Page 172
Setting Deadlock Priority......Page 175
How It Works......Page 176
Table Basics......Page 177
Adding a Column to an Existing Table......Page 181
Changing an Existing Column Definition......Page 182
Creating a Computed Column......Page 183
Reducing Storage for Null Columns......Page 184
Dropping a Table Column......Page 187
Dropping a Table......Page 188
Viewing Collation Metadata......Page 189
Designating a Column’s Collation......Page 190
Keys......Page 191
Creating a Table with a Primary Key......Page 192
How It Works......Page 193
How It Works......Page 194
How It Works......Page 195
How It Works......Page 196
Allowing Cascading Changes in Foreign Keys......Page 197
How It Works......Page 198
Using the IDENTITY Property During Table Creation......Page 199
Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values......Page 200
How It Works......Page 201
Constraints......Page 202
Creating a Unique Constraint......Page 203
Adding a UNIQUE Constraint to an Existing Table......Page 204
How It Works......Page 205
How It Works......Page 206
Disabling and Enabling a Constraint......Page 207
Using a DEFAULT Constraint During Table Creation......Page 208
How It Works......Page 209
Temporary Tables and Table Variables......Page 210
Using a Temporary Table for Multiple Lookups Within a Batch......Page 211
Creating a Table Variable to Hold a Temporary Result Set......Page 212
How It Works......Page 213
Manageability for Very Large Tables......Page 214
Implementing Table Partitioning......Page 215
How It Works......Page 217
Determining the Location of Data in a Partition......Page 218
How It Works......Page 219
Adding a New Partition......Page 220
How It Works......Page 221
How It Works......Page 222
Moving a Partition to a Different Table......Page 223
Removing Partition Functions and Schemes......Page 224
Easing VLDB Manageability with Filegroups......Page 225
Reducing Disk Space Usage with Data Compression......Page 226
How It Works......Page 229
Index Overview......Page 231
Creating a Table Index......Page 233
Enforcing Uniqueness on Non-Key Columns......Page 235
How It Works......Page 236
Viewing Index Meta Data......Page 237
How It Works......Page 238
How It Works......Page 239
Changing an Existing Index with DROP_EXISTING......Page 240
How It Works......Page 241
Allowing User Table Access During Index Creation......Page 242
Using an Index INCLUDE......Page 243
How It Works......Page 244
How It Works......Page 245
Creating an Index on a Filegroup......Page 246
How It Works......Page 247
How It Works......Page 248
How It Works......Page 249
Creating a Full-Text Catalog......Page 251
How It Works......Page 252
Creating a Full-Text Index......Page 253
How It Works......Page 254
Modifying a Full-Text Catalog......Page 255
Modifying a Full-Text Index......Page 256
How It Works......Page 258
Retrieving Full-Text Catalog and Index Metadata......Page 259
Discarding Common Strings from a Full-Text Index......Page 260
Dropping a Full-Text Index......Page 263
Basic Searching......Page 264
How It Works......Page 265
Advanced Searching......Page 266
Using CONTAINS to Search for Inflectional Matches......Page 267
How It Works......Page 268
Returning Ranked Search Results by Meaning......Page 269
Returning Ranked Search Results by Weighted Value......Page 270
How It Works......Page 271
Views......Page 273
Creating a Basic View......Page 274
Querying the View Definition......Page 276
Displaying Views and Their Structures......Page 277
Refreshing a View’s Definition......Page 278
Dropping a View......Page 279
Modifying Data Through a View......Page 280
How It Works......Page 281
Creating an Indexed View......Page 282
How It Works......Page 284
Partitioned Views......Page 285
Creating a Distributed-Partitioned View......Page 286
How It Works......Page 289
Aggregate Functions......Page 291
How It Works......Page 292
Finding the Lowest and Highest Values from an Expression......Page 293
Using Statistical Aggregate Functions......Page 294
Mathematical Functions......Page 295
Performing Mathematical Operations......Page 296
String Functions......Page 297
Converting a Character Value to ASCII and Back to Character......Page 298
How It Works......Page 299
Finding the Start Position of a String Within Another String Using Wildcards......Page 300
How It Works......Page 301
How It Works......Page 302
Replacing a Part of a String......Page 303
Changing Between Lowerand Uppercase......Page 304
Removing Leading and Trailing Blanks......Page 305
Number of Times......Page 306
Returning a Chunk of an Expression......Page 307
Replacing a NULL Value with an Alternative Value......Page 308
Performing Flexible Searches Using ISNULL......Page 309
Returning the First Non-NULL Value in a List of Expressions......Page 310
Date Functions......Page 311
Returning the Current Date and Time......Page 312
How It Works......Page 313
Incrementing or Decrementing a Date’s Value......Page 314
Finding the Difference Between Two Dates......Page 315
Displaying the Integer Representation for Parts of a Date......Page 316
Displaying the Integer Value for Part of a Date Using YEAR, MONTH, and DAY......Page 317
Converting Between Data Types......Page 318
Converting Dates to Their Textual Representation......Page 319
Representing Binary Data in String Literals......Page 320
Evaluating the Data Type Returned by an Expression......Page 321
Ranking Functions......Page 322
Generating an Incrementing Row Number......Page 323
Returning Rows by Rank......Page 324
How It Works......Page 325
Using NTILE......Page 326
Determining the First Day of the Week......Page 327
How It Works......Page 328
Displaying the Nesting Level for the Current Stored Procedure Context......Page 329
How It Works......Page 330
Retrieving the Number of Rows Affected by the Previous Statement......Page 331
Retrieving System Statistics......Page 332
Displaying Database and SQL Server Settings......Page 333
How It Works......Page 334
Returning the Application and Host for the Current User Session......Page 335
Reporting Current User and Login Context......Page 336
IDENTITY and uniqueidentifier Functions......Page 337
How It Works......Page 338
Creating a New uniqueidentifier Value......Page 339
How It Works......Page 340
Conditional Processing......Page 341
Using CASE to Evaluate a Single Input Expression......Page 342
Using CASE to Evaluate Boolean Expressions......Page 343
Using IF...ELSE......Page 344
How It Works......Page 345
Control-of-Flow......Page 346
Using RETURN......Page 347
Using WHILE......Page 348
Using GOTO......Page 350
How It Works......Page 351
Using WAITFOR......Page 352
Cursors......Page 353
Creating and Using Transact-SQL Cursors......Page 355
How It Works......Page 356
Stored Procedure Basics......Page 359
Creating a Basic Stored Procedure......Page 360
How It Works......Page 361
Creating a Parameterized Stored Procedure......Page 362
How It Works......Page 363
Using OUTPUT Parameters......Page 364
How It Works......Page 365
Dropping Stored Procedures......Page 366
Executing Stored Procedures Automatically at SQL Server Startup......Page 367
How It Works......Page 368
Stored Procedure Security......Page 369
How It Works......Page 370
Using EXECUTE AS to Specify the Procedure’s Security Context......Page 371
Recompilation and Caching......Page 374
How It Works......Page 375
How It Works......Page 376
UDF Basics......Page 377
Creating Scalar User-Defined Functions......Page 378
How It Works......Page 381
Creating Inline User-Defined Functions......Page 383
How It Works......Page 384
Creating Multi-Statement User-Defined Functions......Page 385
How It Works......Page 387
Modifying User-Defined Functions......Page 388
How It Works......Page 389
Dropping User-Defined Functions......Page 390
Maintaining Reusable Code......Page 391
Cross-Referencing Natural Key Values......Page 393
How It Works......Page 395
Replacing Views with Multi-Statement UDFs......Page 396
How It Works......Page 398
Creating and Using User-Defined Types......Page 399
Identifying Columns and Parameters with Dependencies on User-Defined Types......Page 401
Dropping User-Defined Types......Page 402
Passing Table-Valued Parameters......Page 403
How It Works......Page 405
Triggers......Page 407
DML Triggers......Page 408
Creating an AFTER DML Trigger......Page 409
How It Works......Page 411
Creating an INSTEAD OF DML Trigger......Page 412
How It Works......Page 414
Handling Transactions Within DML Triggers......Page 415
How It Works......Page 417
How It Works......Page 418
How It Works......Page 419
DDL Triggers......Page 420
Creating a DDL Trigger That Audits Database-Level Events......Page 421
How It Works......Page 422
How It Works......Page 423
Using a Logon Trigger......Page 424
How It Works......Page 425
Viewing DDL Trigger Metadata......Page 426
Modifying a Trigger......Page 427
Enabling and Disabling Table Triggers......Page 428
Limiting Trigger Nesting......Page 429
Controlling Trigger Recursion......Page 430
Setting Trigger Firing Order......Page 431
Dropping a Trigger......Page 433
How It Works......Page 434
CLR Integration......Page 435
When (and When Not) to Use Assemblies......Page 436
Creating CLR Database Objects......Page 438
Writing an Assembly for a CLR Stored Procedure......Page 439
How It Works......Page 440
Compiling an Assembly into a DLL File......Page 442
Loading the Assembly into SQL Server......Page 443
Creating the CLR Stored Procedure......Page 444
How It Works......Page 445
Creating a CLR Scalar User-Defined Function......Page 446
How It Works......Page 447
Creating a CLR Trigger......Page 449
How It Works......Page 450
Modifying an Assembly’s Permissions......Page 451
How It Works......Page 452
Creating XML Data Type Columns......Page 453
How It Works......Page 454
Inserting XML Data into a Column......Page 455
Validating XML Data Using Schemas......Page 456
Retrieving XML Data......Page 458
Modifying XML Data......Page 461
Indexing XML Data......Page 462
How It Works......Page 463
Formatting Relational Data As XML......Page 464
Converting XML to a Relational Form......Page 467
Storing Hierarchical Data......Page 469
How It Works......Page 472
How It Works......Page 473
How It Works......Page 474
How It Works......Page 475
Storing Spatial Data......Page 476
How It Works......Page 478
Querying Spatial Data......Page 479
How It Works......Page 481
Using Join Hints......Page 483
Forcing a HASH Join......Page 484
Using Query Hints......Page 485
Forcing a Statement Recompile......Page 486
Using Table Hints......Page 488
Forcing a SEEK over a SCAN......Page 490
How It Works......Page 492
Viewing System Error Information......Page 493
Creating a User-Defined Error Message......Page 494
How It Works......Page 495
Manually Raising an Error......Page 496
Invoking an Error Message......Page 497
How It Works......Page 498
Trapping and Handling Application Errors......Page 499
Old-Style Error Handling......Page 500
How It Works......Page 501
Error Handling with TRY...CATCH......Page 502
How It Works......Page 503
How It Works......Page 504
Nesting Error Handling......Page 505
How It Works......Page 506
Windows Principals......Page 509
Creating a Windows Login......Page 510
Viewing Windows Logins......Page 511
Altering a Windows Login......Page 512
How It Works......Page 513
SQL Server Principals......Page 514
Viewing SQL Server Logins......Page 516
Altering a SQL Server Login......Page 517
Managing a Login’s Password......Page 518
Managing Server Role Members......Page 519
Reporting Fixed Server Role Information......Page 520
Database Principals......Page 522
Creating Database Users......Page 523
Modifying a Database User......Page 524
Fixing Orphaned Database Users......Page 525
How It Works......Page 526
How It Works......Page 527
How It Works......Page 528
Managing User-Defined Database Roles......Page 529
How It Works......Page 530
Managing Application Roles......Page 531
How It Works......Page 533
Securables, Permissions, and Auditing......Page 535
Permissions Overview......Page 536
Reporting SQL Server Assignable Permissions......Page 537
How It Works......Page 538
Server-Scoped Securables and Permissions......Page 539
Managing Server Permissions......Page 541
Querying Server-Level Permissions......Page 542
Database-Scoped Securables and Permissions......Page 543
Managing Database Permissions......Page 544
Querying Database Permissions......Page 545
How It Works......Page 547
Schema-Scoped Securables and Permissions......Page 548
Managing Schemas......Page 550
Managing Schema Permissions......Page 551
Object Permissions......Page 553
Managing Object Permissions......Page 555
Determining a Current Connection’s Permissions to a Securable......Page 556
Reporting the Permissions for a Principal by Securable Scope......Page 557
How It Works......Page 560
Changing Securable Ownership......Page 561
Allowing SQL Logins to Access Non-SQL Server Resources......Page 562
Auditing SQL Instance and Database-Level Activity of Principals Against Securables......Page 563
Defining Audit Data Sources......Page 564
How It Works......Page 566
Capturing SQL Instance–Scoped Events......Page 567
Capturing Database-Scoped Events......Page 569
How It Works......Page 572
Querying Captured Audit Data......Page 573
How It Works......Page 576
Managing, Modifying, and Removing Audit Objects......Page 577
How It Works......Page 579
Encryption by Passphrase......Page 581
Using a Function to Encrypt by Passphrase......Page 582
How It Works......Page 583
Backing Up and Restoring a Service Master Key......Page 584
Creating, Regenerating, and Dropping a Database Master Key......Page 585
How It Works......Page 586
How It Works......Page 587
Removing Service Master Key Encryption from the Database Master Key......Page 588
Creating an Asymmetric Key......Page 589
Viewing Asymmetric Keys in the Current Database......Page 590
Encrypting and Decrypting Data Using an Asymmetric Key......Page 591
How It Works......Page 593
Creating a Symmetric Key......Page 594
How It Works......Page 595
How It Works......Page 596
Using Symmetric Key Encryption and Decryption......Page 597
How It Works......Page 600
Creating a Database Certificate......Page 601
Viewing Certificates in the Database......Page 602
Backing Up and Restoring a Certificate......Page 603
Managing a Certificate’s Private Key......Page 604
Using Certificate Encryption and Decryption......Page 605
Automatically Opening and Decrypting via a Symmetric Key......Page 607
How It Works......Page 608
Enabling Transparent Data Encryption......Page 609
Managing and Removing TDE......Page 610
How It Works......Page 612
Service Broker......Page 613
Creating a Basic Service Broker Application......Page 614
Enabling Databases for Service Broker Activity......Page 615
Managing Message Types......Page 616
How It Works......Page 617
Creating Contracts......Page 618
Creating Queues......Page 619
Creating Services......Page 621
How It Works......Page 622
Initiating a Dialog......Page 623
How It Works......Page 624
Receiving and Responding to a Message......Page 625
How It Works......Page 627
Ending a Conversation......Page 628
How It Works......Page 629
Prioritizing Service Broker Conversations......Page 630
How It Works......Page 631
Creating the Bookstore Stored Procedure......Page 632
How It Works......Page 634
Remote-Server Service Broker Implementations......Page 635
Enabling Transport Security......Page 637
Enabling Dialog Security......Page 640
Creating Routes and Remote Service Bindings......Page 642
How It Works......Page 645
Capturing Login Commands......Page 646
How It Works......Page 648
Viewing SQL Server Configurations......Page 649
Unknown......Page 0
Changing SQL Server Configurations......Page 651
How It Works......Page 653
Creating, Altering, and Dropping Databases......Page 655
Viewing Database Information......Page 656
How It Works......Page 657
Creating a Database Using File Options......Page 658
How It Works......Page 659
Creating a Database with a User-Defined Filegroup......Page 661
Setting Database User Access......Page 662
How It Works......Page 664
How It Works......Page 665
Detaching a Database......Page 666
Attaching a Database......Page 668
Configuring Database Options......Page 669
Configuring ANSI SQL Options......Page 670
Configuring Automatic Options......Page 672
How It Works......Page 673
How It Works......Page 674
Creating or Changing a Database to Use a Non-Server Default Collation......Page 675
Configuring Cursor Options......Page 676
Enabling Date Correlation Optimization......Page 677
Modifying Database Parameterization Behavior......Page 678
How It Works......Page 680
Enabling Read Consistency for a Transaction......Page 681
How It Works......Page 682
Configuring Database Recovery Models......Page 683
Configuring Page Verification......Page 684
Controlling Database Access and Ownership......Page 685
How It Works......Page 686
How It Works......Page 687
Adding a Data File or Log File to an Existing Database......Page 688
How It Works......Page 689
How It Works......Page 690
Relocating a Data or Transaction Log File......Page 691
How It Works......Page 692
How It Works......Page 693
Setting the Default Filegroup......Page 694
Removing a Filegroup......Page 695
Making a Database or Filegroup Read-Only......Page 696
Viewing Database Space Usage......Page 697
Shrinking the Database or a Database File......Page 699
How It Works......Page 702
Database Integrity Checking......Page 703
Checking Consistency of the Disk Space Allocation Structures with DBCC CHECKALLOC......Page 704
How It Works......Page 705
Checking Allocation and Structural Integrity with DBCC CHECKDB......Page 706
Tables and Constraints......Page 708
Checking Allocation and Structural Integrity of All Tables in a Filegroup Using DBCC CHECKFILEGROUP......Page 709
Checking Data Integrity for Tables and Indexed Views Using DBCC CHECKTABLE......Page 710
Checking Table Integrity with DBCC CHECKCONSTRAINTS......Page 713
How It Works......Page 714
Checking System Table Consistency with DBCC CHECKCATALOG......Page 715
Rebuilding Indexes......Page 716
How It Works......Page 718
How It Works......Page 719
How It Works......Page 720
Changing the Name of a User-Created Database Object......Page 721
How It Works......Page 722
How It Works......Page 723
Identifying Object Dependencies......Page 724
Identifying Referencing and Referenced Entities......Page 726
How It Works......Page 727
Viewing an Object’s Definition......Page 728
How It Works......Page 729
Database Mirroring in Context......Page 731
Database Mirroring Architecture......Page 732
Creating Mirroring Endpoints......Page 734
How It Works......Page 738
Backing Up and Restoring Principal Databases......Page 739
How It Works......Page 740
Creating a Database Mirroring Session......Page 741
Setup Summary......Page 743
Operating Database Mirroring......Page 744
How It Works......Page 745
Performing Failovers......Page 746
How It Works......Page 747
Monitoring Mirror Status......Page 748
Configuring the Connection Timeout Period......Page 749
How It Works......Page 750
Snapshot Basics......Page 751
Creating and Querying Database Snapshots......Page 752
Removing a Database Snapshot......Page 753
Recovering Data with a Database Snapshot......Page 754
How It Works......Page 756
Linked Server Basics......Page 757
Creating a Linked Server to Another SQL Server Instance......Page 758
Configuring Linked Server Properties......Page 759
Dropping a Linked Server......Page 761
Adding a Linked Server Login Mapping......Page 762
How It Works......Page 763
Executing Distributed Queries Against a Linked Server......Page 764
How It Works......Page 765
How It Works......Page 766
Executing Ad Hoc Queries Using OPENROWSET......Page 767
How It Works......Page 768
Reading Data from a File Using OPENROWSET BULK Options......Page 769
How It Works......Page 771
Query Performance Tuning......Page 773
Query Performance Tips......Page 774
How It Works......Page 776
Viewing Estimated Query Execution Plans Using Transact-SQL Commands......Page 777
How It Works......Page 779
Viewing Execution Runtime Information......Page 780
Viewing Performance Statistics for Cached Query Plans......Page 782
How It Works......Page 783
Viewing Aggregated Performance Statistics Based on Query or Plan Patterns......Page 784
How It Works......Page 785
Identifying the Top Bottleneck......Page 786
Identifying I/O Contention by Database and File......Page 787
Index Tuning......Page 788
Displaying Index Fragmentation......Page 790
How It Works......Page 791
How It Works......Page 793
Manually Creating Statistics......Page 794
Creating Statistics on a Subset of Rows......Page 795
Updating Statistics......Page 796
Generating and Updating Statistics Across All Tables......Page 797
Viewing Statistics Details......Page 799
Miscellaneous Techniques......Page 800
Using an Alternative to Dynamic SQL......Page 801
How It Works......Page 802
Forcing SQL Server to Use a Query Plan......Page 803
Applying Hints Without Modifying Application SQL......Page 805
How It Works......Page 808
Creating Plan Guides from Cache......Page 809
Checking the Validity of a Plan Guide......Page 811
Parameterizing a Non-parameterized Query Using Plan Guides......Page 812
How It Works......Page 814
Limiting Competing Query Resource Consumption......Page 815
How It Works......Page 821
Creating a Backup and Recovery Plan......Page 823
Making Backups......Page 825
Performing a Basic Full Backup......Page 827
Compressing Your Backups......Page 828
Naming and Describing Your Backups and Media......Page 830
Configuring Backup Retention......Page 831
How It Works......Page 832
Using a Named Backup Device......Page 833
Mirroring Backup Sets......Page 835
How It Works......Page 836
Performing a Transaction Log Backup......Page 837
How It Works......Page 838
Backing Up Individual Files or Filegroups......Page 839
Performing a Partial Backup......Page 841
Viewing Backup Metadata......Page 842
How It Works......Page 843
Restoring a Database from a Full Backup......Page 844
Restoring a Database from a Transaction Log Backup......Page 846
Restoring a Database from a Differential Backup......Page 849
Restoring a File or Filegroup......Page 850
How It Works......Page 851
Performing a Piecemeal (PARTIAL) Restore......Page 852
Restoring a Page......Page 853
Identifying Databases with Multiple Recovery Paths......Page 854
How It Works......Page 856
Index......Page 857