Microsoft SQL Server 2000 Database Development From Scratch

Book

Description

Copyright 2001

Dimensions: 7.375 X 9.125

Pages: 496

Edition: 1st

Book

ISBN-10: 0-7897-2447-2

ISBN-13: 978-0-7897-2447-2

Microsoft SQL Server Database Development from Scratch is a road map to database management, implementation, and administration. Author Rob Hawthorne teaches readers how to create, maintain, and increase performance within the application. Concepts and techniques are reinforced as readers create a spy tracker database.

To restore the SQLSpyNet database you will need to use the above code within Query Analyzer of course!

1: RESTORE DATABASE SQLSpyNet

2: FROM DISK = 'Download Location of SQLSpyNet.bak'

3: WITH REPLACE,

4: MOVE 'SQLSpyNet_Data' TO 'Location of Data files with the name

SQLSpyNet_Data.mdf',

5: MOVE 'SQLSpyNet_Log' TO 'Location of Log files with the name

SQLSpyNet_Log.mdf',

6: STATS = 10

Just remeber though on Line 2 you will need to replace "Download Location of
SQLSpyNet.bak" with the path that you downloaded the SQLSpyNet.bak file to
For example'C:\Temp\SQLSpyNet.bak'. The same for lines 4 & 5. Replace the
text I have there with the paths that you wish to save the .mdf and .ldf
files to. The following code snippet gives you an example of what your code
may look like after changing the text as I have specified.

RESTORE DATABASE SQLSpyNet

FROM DISK = 'C:\Program Files\Microsoft SQL

Server\MSSQL$MYSQLSERVER\BACKUP\SQLSpyNet.bak'

WITH REPLACE,

MOVE 'SQLSpyNet_Data' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL$MYSQLSERVER\DATA\SQLSpyNet_Data.mdf',

MOVE 'SQLSpyNet_Log' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL$MYSQLSERVER\DATA\SQLSpyNet_Log.mdf',

STATS = 10

For more information on restoring databases refer to Chapter 10 in the book.
The process is described in a little more detail there. The code listings on the site have been made available to make life a little easier for you. However you should note that in some instances you cannot run the whole code file for whole a chapter in one go. Instead the
recommended approach is to run each code listing seperately. The reason for this is the code appears as it does in the book. However SQL Server requires that CREATE statements i.e. CREATE PROCEDURE are the first lines of code in batch.

Also you need to be aware of the placeholders in the listings. You
may find in the code listings that I have a direct reference to the physical
location of a file on disk. You NEED to change this before the code will
run. For example if I have BACKUP DATABASE SQLSPyNet TO DISK =
'x:\SQLSpyNet.bak' you would need to change the "x:\" to the actual path
where you would like the backup to be saved to.

Using the Data Definition Language (DDL) to Create Databases and Objects.

Building Tables for Our Spies. Revisiting the Analysis of the SQL Spy Net Application.

Bringing Our Data Model and Database Together.

Developing the First Database Table for the SQL Spy Net Application. Creating Tables the Fun Way-With Code. Filling in the Blanks.

Summary. Next Steps.

4. Manipulating Data with Transact-SQL.

What Is This SELECT Statement Then?

The First Building Block for SELECT. Where Does the Data Come FROM? Limiting Data with WHERE. Put Those Spies in Order!

Let's Get Some Data in with the INSERT Statement.

Defining the Table and Columns We Want to Insert Into. Let's Put In Some New Data!

Keeping Everything Up-to-Date.

Which Table, Please? Correcting Data to Fix Data Problems. WHERE to Put the New Value.

When a Good Spy Dies...DELETE 'em. What Else Can We Do with Transact-SQL Besides DMLs and DDLs?

Declaring Variables. Assigning Values to Our Variables with the SET Statement. The IF Test--Is What We Have in Our Variable What We Expect? Using WHILE Loops.

Summary. Next Steps.

5. Pushing the Boundaries of Our DDL Knowledge to View and Update Data.

Adding Information About Our Spies. Finding a Good View.

Getting Your First Look at a View. Looking at JOINs. Comparing Views to Tables. Restrictions on Views.

Streamlining Updates with Stored Procedures.

Creating the Stored Procedure Code. Checking the Table for Places to Fill. Letting the Stored Procedure Do the Work. Creating the Stored Procedure to Insert into Both the Person and BadGuy Tables. Checking For and Filling In NULLs. Guidelines for Using Stored Procedures.

Pulling Triggers to Catch Events.

Creating a Trigger to Catch Double Spies. Testing the Trigger. Setting a Trigger on the Spy Table.

Dropping Objects All Over the Place. Going Round and Round Using Cursors. Summary. Next Steps.

6. Getting Clearer Results with Functions.

Understanding the Role of Functions. Using Built-In Functions.

Using CONVERT to “Make Money”. COUNT-ing Spies. Using the SUM Function to Tally Paychecks. Putting STUFF into Strings.

Creating Your Own Functions to Manipulate Data.

Tweaking Dates from Around the Globe. Executing a User-Defined Function.

Considerations for Developing a Security Strategy. Summary. Next Steps.

10. Ensuring Data Availability.

Creating a Database Backup Strategy.

How to Prevent Losing Everything. Using Transaction Logs in Backup and Recovery. Choosing the Appropriate Recovery Model. Finding the (Right) Time. Where Do I Back Up To? What Do I Back Up? Backing Up Spy Net Using Enterprise Manager. Backing Up Spy Net Using Transact-SQL in Query Analyzer.

Restoring Spy Net.

How to Get It All Back. Applying Transaction Logs. Restoring Spy Net Using Enterprise Manager. Restoring Spy Net Using Transact-SQL in Query Analyzer.

Finalizing Your Plan.

Keep Track of Copies. Mark the Transaction Log. Create a Warm Server. Create a Checklist of Resources and Practices. Make and Follow Your Recovery Steps.

Using the Tried and True Method of ODBC (MSDASQL). Using the New-Fangled OLE DB Provider for SQL Server (SQLOLEDB).

Building the SpyNet User Interface.

Determining the SpyNet Web Pages. Setting Up Your Machine to Run the SpyNet Application. Setting Up Our Web Site to Run Under PWS. Creating a New User to Establish a Connection. Creating Our First Page, the Global.asa. Creating the Default.htm Page. Building the Include Pages. Validating the User's Login. Greeting Users with the Welcome.asp Page. Creating the Search.asp Page to Retrieve Data from SpyNet.

Selecting the Type of Installation. Installing the Application Following the Step-By-Step Wizard. What Happens If I Select the Custom Install Option? Checking the Installation's Success. Configuring SQL Server 2000.

Connecting to SQL Server 2000 for the First Time. Using a Wizard to Connect to SQL Server 2000. How Does It All Work?

Appendix C. Where to Go from Here.

Microsoft Certification.

How to Become a Microsoft-Certified Professional (MCP). How to Become a Microsoft-Certified Solution Developer (MCSD). How to Become a Microsoft-Certified Systems Engineer (MCSE). How to Become a Microsoft-Certified Database Administrator (MCDBA). How to Plan for Microsoft Certification.