SQL Server 2000 Backup and Recovery

by Desai, Anil

• ISBN13: 9780072130270
• ISBN10: 007213027X
• Edition: 1st
• Format: Paperback
• Copyright: 2001-01-01
• Publisher: MCGRAW HILL BOOK COMPANY

Summary

(Osborne) Explains in detail how to develop a backup and recovery strategy on SQL Server 2000, based on individual business requirements. Also shows how to select hardware and software, plan for data storage, perform backups, and execute data restore procedures. A complete guide. Softcover.

Table of Contents

Acknowledgments	iv
Introduction	xiii
Part I Developing a Data Protection Strategy
Evaluating Data Protection Requirements	3	(36)
Why Protect Your Data?	5	(2)
Information Is Valuable	5	(1)
Re-Creating Data Is Difficult and Costly	6	(1)
Downtime Is Expensive	6	(1)
Public Perception Can Make or Break Your Business	7	(1)
Threats to Your Data	7	(4)
Hardware and Software Failures	8	(1)
People with Good Intentions	9	(1)
People with Bad Intentions	9	(1)
Natural Disasters	10	(1)
Other Potential Problems	11	(1)
In an Ideal World	11	(2)
Challenges in the Real World	13	(3)
Financial Constraints	13	(1)
Staffing Constraints	13	(1)
Technical Constraints	14	(1)
Lack of Understanding	14	(1)
The Speed of E-Business	15	(1)
Political Issues	15	(1)
Summing It All Up	16	(1)
Evaluating Your Environment	16	(13)
Why Evaluate?	16	(1)
Business Applications	17	(1)
The Network Infrastructure	18	(1)
Workstations (Clients)	18	(1)
Servers	18	(6)
Relational Database Servers	24	(4)
Human Resources	28	(1)
Evaluating Your Business Requirements	29	(8)
Deciding What to Protect	29	(1)
Estimating Your Data Storage REquirements	30	(1)
Availability of Crucial Information	31	(2)
Backup Requirements	33	(1)
Recovery Requirements	33	(1)
Budgeting for Data Protection	33	(3)
A Sample Business Requirements Worksheet	36	(1)
Summary	37	(2)
Developing the Data Protection Plan	39	(46)
Data Protection Methodologies	41	(10)
Backup Scenarios	42	(7)
Backup Operations	49	(2)
Choosing the Right Method	51	(1)
Backup and Recovery Technology	51	(3)
Hardware Solutions	51	(2)
Software Solutions	53	(1)
Disaster Recovery	53	(1)
Business and Management Solutions	54	(8)
Management Solutions	54	(1)
Getting Help: Outsourcing	55	(1)
Organizing Backups and Media	55	(1)
Developing IT Policies	56	(2)
The People Factor	58	(1)
Educating the End Users	58	(2)
Managing Costs	60	(2)
General IT Best Practices	62	(6)
Determining Security Requirements	63	(2)
Standardization	65	(2)
Change and Configuration Management	67	(1)
Knowledge Management	68	(1)
Controlling Resource Usage	68	(1)
Creating the Data Protection Plan	68	(4)
Studying Your Environment	69	(1)
Defining Backup Policies	69	(1)
Using the Iterative Approach and Setting Milestones	70	(1)
Getting Others Involved	70	(2)
Archiving	72	(1)
Documenting the Plan	72	(1)
Implementing the Data Protection Plan	72	(2)
Training the Right People	72	(1)
Developing a Schedule	73	(1)
Delegating Responsibility	73	(1)
Reviewing and Maintaining the Plan	74	(1)
Ensuring That the Plan Is Flexible	74	(1)
Ensuring Physical Security	74	(1)
What Would an Intruder Do?	75	(1)
Establishing External Security Audits	75	(1)
A Sample Data Protection Plan	75	(6)
Summary	81	(4)
Part II Understanding Data Protection Technology
Windows NT/2000 Security and Data Protection	85	(80)
Security and Data Protection Best Practices	86	(10)
Know the Ways of Your Enemies	87	(1)
Physical Security	88	(1)
Managing User Security	88	(4)
Protecting Against Viruses and Malicious Code	92	(1)
Defining a Backup Schedule	93	(1)
Security Management Tools	94	(2)
Windows NT 4 Data Protection	96	(27)
Windows NT Backup	96	(2)
The Windows NT Emergency Repair Disk	98	(1)
Implementing Strong Password Policies	99	(1)
Auditing	100	(5)
Using Windows NT Diagnostics	105	(1)
Windows System Policies	105	(9)
Securing the File System	114	(1)
Fault Tolerance: RAID	114	(4)
Windows NT Network Security	118	(3)
Remote Access Security	121	(2)
Client-Side Security	123	(1)
Windows 2000 Data Protection	123	(17)
Active Directory	125	(5)
Backup and Recovery Tools	130	(2)
File System Changes	132	(2)
Disk Management and Dynamic Volumes	134	(2)
Network-Level Security	136	(3)
Improved Authentication	139	(1)
Remote Storage	139	(1)
Windows NT/2000 Data Recovery	140	(3)
Recovering Data	141	(1)
Fixing Corrupt Boot Files	141	(1)
Performing a Complete System Restoration	142	(1)
Monitoring and Optimizing Performance	143	(17)
Performance Monitoring Methodology	143	(1)
Windows NT/2000 Performance Tools	144	(6)
Windows NT/2000 Task Manager	150	(6)
Windows NT/2000 Network Monitor	156	(2)
Windows 95/98/ME System Monitor	158	(2)
Staying Current	160	(4)
Windows NT Service Packs	160	(1)
The Windows NT Option Pack	160	(1)
Windows 2000 Updates	161	(1)
Security-Related Hot Fixes, Patches, and Bulletins	162	(2)
Downloading Software Updates	164	(1)
Summary	164	(1)
SQL Server 2000 Architecture	165	(90)
Relational Databases	167	(9)
Database Objects	168	(5)
Domain, Entity, and Referential Integrity	173	(1)
Structured Query Language	174	(2)
SQL Server Architecture	176	(19)
Different Editions of SQL Server 2000	177	(1)
SQL Server Services	177	(4)
Data Storage Architecture	181	(3)
Backup and Recovery Architecture	184	(2)
New Features in SQL Server 2000	186	(3)
The Transact-SQL Language	189	(5)
Locking and Concurrency	194	(1)
Network Communications	194	(1)
Working with SQL Server 2000	195	(7)
Enterprise Manager	196	(1)
Database Schemas	197	(1)
Data Transformation Services	198	(2)
SQL Query Analyzer	200	(2)
SQL Server Security	202	(25)
Server Logins	204	(5)
Database User Permissions	209	(1)
Roles	210	(7)
Object Permissions	217	(4)
Auditing	221	(4)
SQL Server Security Best Practices	225	(2)
Performance Monitoring and Optimization	227	(19)
The Server Level	227	(4)
The Database Level	231	(3)
The Query Level	234	(6)
Scalability and Benchmarks	240	(1)
Scheduling Jobs with SQL Server Agent	240	(5)
Choosing the Right Tool	245	(1)
Developing SQL Server Applications	246	(3)
n-Tier Client/Server Architecture	247	(1)
Accessing Data from Applications	247	(1)
Programming Options	248	(1)
SQL Server 2000 Analysis Services	249	(5)
Data Warehousing	249	(1)
Analysis Services and Online Analytical Processing (OLAP)	250	(2)
Data Mining	252	(1)
English Query	253	(1)
Summary	254	(1)
Evaluating Data Protection Solutions	255	(50)
Evaluating Your Environment	256	(11)
Business Issues	257	(1)
Estimating the Data Volume	258	(5)
The ``Recovery Window''	263	(1)
Live vs. Offline Database Backups	263	(2)
Leveraging Your Existing Resources	265	(1)
Network Topology	266	(1)
Choosing Server Hardware	267	(10)
Processor(s)	267	(1)
Memory	268	(1)
Disk Subsystems	269	(3)
RAID	272	(3)
Uninterruptible Power Supplies	275	(2)
Evaluating Backup Hardware	277	(8)
Characteristics of Backup Devices	277	(2)
Data Buses	279	(2)
Media Options	281	(2)
Advanced Backup Solutions	283	(1)
Vendors of Third-Party Hardware	284	(1)
Evaluating Backup Software	285	(4)
Software Features	286	(1)
Special Modules	287	(1)
Disaster Recovery Options	288	(1)
Licensing Models	288	(1)
Third-Party Software Products	289	(10)
Vendors of Backup Software	290	(1)
Data Protection Utilities	291	(4)
Benchmarking Software	295	(4)
Making Sound Business Decisions	299	(3)
Total Cost of Ownership (TCO)	300	(1)
Return on Investment (ROI)	301	(1)
Summary	302	(3)
Part III Backup Procedures and Methods
Performing Database Backups	305	(86)
Designing SQL Server Storage	307	(19)
Storage Estimation Tools	307	(4)
RAID	311	(1)
Files and Filegroups	312	(14)
Optimizing Storage Design	326	(1)
SQL Server 2000 Backup Methods	326	(15)
Offline vs. Online Backups	327	(1)
Taking Databases Offline	328	(1)
Attaching and Detaching Databases	329	(1)
Comparing Backup Schemes	330	(6)
Choosing Database Recovery Models	336	(2)
Establishing a Backup Schedule	338	(2)
Additional Backup Solutions	340	(1)
SQL Server 2000 Backup and Recovery Architecture Enhancements	341	(8)
Fast Differential Backup	342	(1)
Snapshot Backups	342	(1)
Log-Shipping	343	(1)
Copy Database Wizard	344	(4)
Logical Log Marks	348	(1)
Implementing SQL Server Backups	349	(36)
Preparing for Backups	349	(6)
The Database Maintenance Plan Wizard	355	(10)
Modifying Database Maintenance Plans	365	(1)
Performing Backups	366	(19)
Optimizing Backup Performance	385	(3)
The Optimization Process	385	(1)
Using Performance Monitor	386	(1)
A Few Final Tips	386	(2)
Summary	388	(3)
Restoring Databases	391	(50)
Planning for Data Recovery	392	(8)
Establishing Recovery Procedures	392	(1)
Performing Test Restorations	393	(4)
Delegating Roles and Responsibilities	397	(2)
Creating Contingency Plans	399	(1)
Data Loss Scenarios	400	(18)
Hardware Failures	400	(1)
Performing a Complete Reinstallation	401	(1)
Disaster Recovery Options	402	(1)
Database and Server Corruption	402	(8)
Other Scenarios	410	(8)
Resetting Corrupted or Suspect Databases	418	(1)
Data Restoration Options	418	(4)
Database Recovery	419	(1)
Using Differential Backups	420	(1)
Applying Transaction Logs	421	(1)
Recovering Files and Filegroups	421	(1)
Restoring to a Specific Point in Time	422	(1)
Recovering SQL Server Databases	422	(18)
Process Overview	423	(1)
Preparing for a Restore	423	(1)
Using Enterprise Manager	424	(6)
Using Transact-SQL	430	(10)
Summary	440	(1)
Backup and Recovery Scenarios, Tools, and Techniques	441	(72)
Simple Backup for a Small Database	442	(11)
Requirements and Scenario Overview	442	(1)
Analysis	443	(1)
Full Backups Every Four Hours	444	(6)
Full Backups with Transaction Log Dumps	450	(2)
Alternative Solutions	452	(1)
Backup Options for a Large Database	453	(15)
Requirements and Scenario Overview	454	(1)
Analysis	454	(1)
Solution: Full, Differential, and Transaction Log Backups	455	(12)
Alternative Solutions	467	(1)
Summary of Scenarios	468	(1)
Automating Tasks with SQL Server Agent	468	(21)
Configuring SQL Server Agent	468	(6)
Defining Operators	474	(3)
Scheduling Jobs	477	(4)
Defining Alerts	481	(4)
Configuring SQL Mail	485	(2)
Multiserver Administration	487	(2)
Importing, Exporting, and Transferring Data	489	(17)
Generating SQL Scripts	490	(2)
Loading Data	492	(7)
The SQLMaint Utility	499	(7)
Additional Scripting Tools and Techniques	506	(4)
Resetting ``Suspect'' Databases	506	(1)
Restarting Interrupted Backup and Restore Operations	506	(1)
Managing the Size of the Msdb Database	507	(1)
Creating a Script That Writes Scripts	507	(1)
Centralizing Server Registrations	508	(1)
Changing the Name of a SQL Server Installation	509	(1)
Summary	510	(3)
Part IV Advanced Data Protection Methods
Advanced Data Protection: Replication, Log-Shipping, and Clustering	513	(94)
Overview: Scalability, Reliability, Performance	514	(3)
Scaling Up vs. Scaling Out	515	(2)
Performance	517	(1)
Reliability	517	(1)
SQL Server's Replication Model	517	(14)
Replication Architecture	519	(1)
Planning for Replication	520	(1)
Creating Publications	521	(1)
Replication Server Roles	522	(3)
Replication Operations	525	(1)
Replication Methods	526	(2)
Replication Agents	528	(1)
Enhancements in SQL Server 2000 Replication	529	(2)
Implementing SQL Server Replication	531	(33)
Enabling Replication	532	(11)
Creating and Managing Publications	543	(12)
Creating Push and Pull Subscriptions	555	(1)
Using Transact-SQL	556	(2)
Network Considerations	558	(1)
Working in Heterogeneous Environments	559	(1)
Monitoring SQL Server Replication	559	(5)
Strategies for Backing Up Replication	564	(1)
SQL Server Log-Shipping	564	(23)
Overview of Standby Servers	565	(1)
Log-Shipping Scenarios	566	(3)
Implementing SQL Server Log-Shipping	569	(18)
SQL Server Clustering	587	(18)
Planning for Clustering	589	(4)
Clustering Technology	593	(5)
Microsoft Clustering Solutions	598	(6)
The Future of Clustering Technology	604	(1)
Summary	605	(2)
Case Studies: Solving Business Challenges	607	(20)
Mom & Pop's Bloomin' Garden Shop	609	(4)
Introduction	609	(1)
The Story	610	(2)
Analysis	612	(1)
Alien Minds, Inc	613	(4)
Introduction	613	(2)
The Story	615	(1)
Analysis	616	(1)
Java the Hutt, Inc.	617	(4)
Introduction	617	(1)
The Story	618	(3)
Analysis	621	(1)
Xenico Pharmaceuticals	621	(4)
Introduction	622	(1)
The Story	623	(2)
Analysis	625	(1)
Summary	625	(2)
A SQL Server 2000 Backup and Recovery Reference	627	(46)
System Stored Procedures	628	(8)
Useful Stored Procedures	628	(5)
Useful Extended Stored Procedures	633	(3)
DBCC Commands	636	(6)
Validation Commands	637	(3)
Maintenance Commands	640	(2)
Status Commands	642	(1)
Miscellaneous Commands	642	(1)
Database Options	642	(4)
Information Schema Views	646	(3)
SQL Server Configuration Options	649	(4)
Backup and Recovery System Tables	653	(18)
System Tables in Master Database	656	(1)
System Tables in All User Databases	657	(1)
Backup Tables in Msdb Database	658	(3)
Restore Tables in Msdb Database	661	(1)
Database Maintenance Plan Tables in Msdb Database	662	(2)
Log-Shipping Tables in Msdb Database	664	(3)
Job Tables in Msdb Database	667	(4)
Summary	671	(2)
B Resources for More Information	673	(14)
Microsoft Resources	674	(6)
TechNet	674	(1)
Microsoft Developer Network (MSDN)	675	(1)
Knowledge Base	676	(1)
Seminar Online	677	(1)
Phone-Based Technical Support	677	(1)
Training and Certification	678	(2)
IT Resources	680	(3)
Web Sites	680	(1)
Magazines	680	(3)
USENET Newsgroups	683	(4)
Index	687