Before I actually start explaining and demonstrating how to build SQL Server Reporting Service (SSRS) reports there are few things you must have installed or restored before you can begin. As mentioned in my previous post, SQL Server 2012 for Beginners, this is a journey. While the first part of the journey is focused on SSRS, there is a possibility that we may take short excursions that explain or demonstrate other features and capabilities of SQL Server. As with the case of this post.

Let’s get started.

Prerequisites

1. SQL Server 2012 Database Engine sp1
2. SQL Server Management Studio sp1
3. SQL Server Data Tools sp1
4. SQL Server Reporting Services 2012 sp1
5. Restored Copy of the ContosoSchoolsDW database

I am going to go against the main purpose of these blog posts by assuming that the first 4 have already been installed, configured, and that you have been given access to the Database Engine and the Report Server. With that said, I am going to back track eventually and explain how to install and configure each in future posts. Hopefully, that is ok for now. If not, please feel free to contact me and I would be happy to direct you to resources that could assist you in getting these set up. One thing that I can suggest is that you go out and pre-order a copy SQL Server 2012 Step By Step (SHAMELESS PLUG), which explains how to setup and configure each.

Now that you have all the required tools in place, the next step is the restore the database that will be used throughout the demonstrations. You can click HERE to download the backup file. Ensure that the account that is specified to run the SQL Server Database engine has access to the download location.

Watch the Video if you prefer:

Restore a Database

Using SQL Server Management Studio (SSMS) you can restore a database several ways. In this posting I will explain how to restore a database using SSMS Graphical User Interface (GUI) and TSQL. Since these are introductory posts the main focus will be on the GUI, however I will provide a sample (Transact SQL) T-SQL script that restores the database.

Restoring a Database with SSMS

1. Open SSMS
2. The Connect to Server window will open. Select Database Engine from the Server type drop down list.
3. Enter your server name in the Server name drop down list.
4. Select your Authentication method from the Authentication drop down list.
5. If you selected SQL Server Authentication you must enter your User name and Password in the corresponding checkboxes.

   

    

6. Click the button labeled Connect.
7. The Object Explorer should be open on the left-side of the SSMS window. If it is not press F8 to open it or in the menu select View | Object Explorer.
8. Expand the SQL Server instance that you are connected to.

   

    

9. Right-click on the Databases folder and select Restore Database from the context menu that appears.

   

10. The Restore Database window will open.
11. Ensure that General is selected in the Select a page pane located in the left section of the window.
12. The options of this page are divided into three sections, Source, Destination and Restore Plan.
13. In the Source section select the radio button labeled Device.
14. Click the Ellipses button located to the right of the Device textbox and the Select backup devices window will open.
15. Ensure that File is selected in the Backup media type drop down list.
16. Click the button labeled Add and the Locate Backup File window will open.
17. Navigate to the location where you download the ContosoSchoolDW.bak file and select it.

    

18. Click the OK button.
19. You will return to the Select backup devices window.

    

20. Click OK.
21. Back on the main Restore Database window your screen should resemble the following:

    

22. Select Files in the Select a page pane.
23. Now this is where things can get tricky if you are not familiar with restoring SQL Server databases.
24. Expand the Restore As column and ensure that the path specified in each row does exist. The path specified in this column may be different than the one specified in the Original File Name column.

    

25. Click OK and the database restore will begin.

Once the database restore is complete you can begin querying its contents. If you receive any errors or have any questions about the contents of this post please feel free to post a comment here.

Restoring a Database using TSQL

1. Open SSMS.
2. The Connect to Server window will open. Select Database Engine from the Server type drop down list.
3. Enter your server name in the Server name drop down list.
4. Select your Authentication method from the Authentication drop down list.
5. If you selected SQL Server Authentication you must enter your User name and Password in the corresponding checkboxes.
6. Click the button labeled Connect.
7. In the menu bar locate and click New Query.
8. Enter and execute the following query:

   USE

   [master]

   RESTORE DATABASE [ContosoSchoolsDW]

   FROM DISK = N’C:\SQL Server\backup\ContosoSchoolsDW.bak’

   WITH FILE = 1,

   MOVE N’CrescentSchools’ TO N’C:\SQLServer\data\CrescentSchools.mdf’,

   MOVE N’CrescentSchools_log’ TO

   N’C:\SQL Server\log\CrescentSchools_log.ldf’,

   STATS = 5

   GO

   Note: If you have already restored the ContosoSchoolDW you will receive and error if you execute the query.

In the next post, Creating a SQL Server Reporting Service Project, I will discuss the differences between a solution and a project and provide the steps to create both.

Talk to you soon,

Patrick LeBlanc, Microsoft, Technical Solutions Professional SQL Server and Business Intelligence

Founder www.sqllunch.com