This exercise is taken from our 70-432 - Microsoft SQL Server 2008 Implementation and Maintenance course and starts our series of free demos from our paid courses. If you like this demo and want to get more involved with Microsoft SQL Server 2008 then our SQL Server courses are just for you.

Our SQL Courses have the following benefits

• No time restrictions
• Additional lab layouts
• Dozens more tasks
• Extremely cost effective

This is a modified sample from our 70-432 - Microsoft SQL Server 2008 Implementation and Maintenance course, it has been modified to enable it to flow correctly as a standalone module.

In this exercise you will learn how to create and populate a database using T-SQL commands. You will then further your T-SQL skills by creating a database snapshot, looking at the differences in the files between the active and snapshot databases, then finally modifying some data and restoring the snapshot. Please refer to the Microsoft website for more information on database snapshots at Microsoft Technet which will help you gain a further understanding of this technology.

Create, Drop and Revert operations on snapshot database overview

A Database Snapshot is a read-only, static view of a database without any uncommitted transactions. The uncommitted transactions are rolled back to make the database snapshot transactionally consistent.

When you create a snapshot database, that database will be allocated to an empty sparse file . Whenever there are changes to the original data pages, the original pages are moved to the sparse file.

If you access the database snapshot, it will be read from the sparse file and from the original database data pages which have not changed.

Creating a database

Use the following steps to create a database using T-SQL statements, these statements will create the database then insert some data so that a snapshot can be made.

Step 1

If you haven't done so already, power on and connect to SQL Server 1. Ensure you login as administrator (leave the auto-login checkbox checked). Then open up SQL Management Studio and create a new query. To do this, double-click the SQL Server Management Icon on the desktop of SQL Server 1.

Next click the Connect button in the connect to server dialog once the management studio has loaded.

To create a new query, click the New Query button in the top left hand side of SQL Server Management Studio.

You should now have a screen similar to the below:

The Query window allows you to type and execute SQL commands. To type a command, place your cursor in the large white space in the middle of the screen and type in the respective T-SQL command that you require. To execute the command hit the Execute button which has the red exclamation mark on it in the toolbar at the top of the screen.

Use the following T-SQL commands to create the database, make sure you clear the query window after each one (alternatively when you are more confident with SQL commands you can enter in a number of them at the same time to be executed sequentially for example you could create the new database, table and insert the data all in the same query!):

	USE master
    GO

Remember after each command to hit execute!

These commands will create a database called MyFirstDatabase:

     CREATE DATABASE MyFirstDatabase
     GO

Next you want to select this database to run the next set of SQL commands on (in the screenshot below I have entered in the 3 of the statements below, Select the database, create the table and insert the data).

	 USE MyFirstDatabase
     GO

Next we need to create a table in the database so that we can populate it with some data.

    CREATE TABLE MyFirstTable (ID INT, FirstName VARCHAR(128), LastName VARCHAR(128))
	GO 

Now lets populate the table with a few rows of data.

    INSERT INTO MyFirstTable VALUES(1, 'Rupert', 'Bear')
    INSERT INTO MyFirstTable VALUES(2, 'Postman', 'Pat')
    INSERT INTO MyFirstTable VALUES(3, ' Thomas', 'Tank-Engine')
    GO

Before continuing, lets have a look at the table data. To do this, we can expand in object explorer Databases > MyFirstDatabase > Tables then Right-click the table and select Select top 1000 rows

You can see the data we have entered in:

Finally we create the snapshot of the database using the following commands. Ensure you are back at your query window (or open a new query)

    CREATE DATABASE MySnapshot ON (Name ='MyFirstDatabase', FileName='c:\myfirstdatabase_snapshot.ss1') 
    AS SNAPSHOT OF MyFirstDatabase
    GO  

Lets take a look at the differences in the two database files

MyFirstDatabase (Located under C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA):

Snapshot of MyFirstDatabase (Located under C :\):

Notice the Size on disk properties of the two files!

Revert Operation

To be able to perform a revert operation; the user must have the RESTORE DATABASE permissions on the source database, this is not a problem for our example, but its something to keep in mind in a real environment.

Before you restore the snapshot, why not add some additional data, or modify the existing data in the table?

Use SSMS (SQL Server Management Studio) or a T-SQL command to modify some data, in this example I have chosen to use SSMS by right clicking on our table and selecting Edit top 200 rows you can then edit the cells rather like a spread sheet:

As you can see I have modified all the rows:

Step 1

To restore a database from a snapshot database use the following T-SQL statements (remember use a New Query or your existing query window):

	USE master
    GO 
    RESTORE DATABASE MyFirstDatabase FROM DATABASE_SNAPSHOT = 'MySnapshot'
    GO

When you execute this command, you may see and error:

Make sure that you have closed your edit window before hand (in fact you may have to close and re-open SQL Server Management Studio)!

You can see our updated rows have been reverted:

Also try

• Why not try to use the Drop Database T-SQL command to drop the snapshot.

Summary

In this exercise learned how to create a database, add a table and some fictitious data. You then learned how to create a snapshot of this data before finally restoring the snapshot.

You also saw the differences in the file types between the live database and the snapshot of the database which is important to understand.

And finally

The above exercise gives you an insight to what you can achieve in a Practice-Lab. Being able to learn, discover and explore new topics and tools without the fear of compromising production environments.

If you are interested in more topics like the above then have a look at our 70-432 - Microsoft SQL Server 2008 Implementation and Maintenance Practice-Lab course.

Alternatively look at purchasing access to our Practice-Lab library that covers all of our supported technologies.

We recommend repeating these exercises as it will not only help build your confidence in the subject matter but will open your mind to the other capabilities. Do not feel afraid to experiment in our labs!

If you wish to comment on this Practice-Lab please send an email to Support@Practice-IT.co.uk with your feedback.