SQL Server Reporting Services 2008 (SSRS) Databases- ReportServer & ReportServerTempDB.

When reporting service is set up there are two default databases created which are

1. ReportServer

2. ReportServerTempDB.


Reportserver database contains a wide range of information which is briefly listed below.

1. Subscriptions

2. Schedules

3. Data sources

4. Folder

5. Keys

6. History

7. Notifications and much much more.

I would suggest looking at the tables which are contained in the ReportServer database to get a good understanding about all the data which is stored and within and I have listed below.



































I would always recommend setting a maintenance plan to regularly backup this database due to all the important data that is held within. Do not attempt to try and modify the table structure within these tables as you will likely cause an error.

ReportServerTempDB stores temporary data from the ReportServer DB which includes cached reports, session information….. This data is still important and it should still be backed up to aid speedy recovery.

Adding SQL Server Reporting Services 2008

Adding SQL Server Reporting Services 2008

Before installing SQL Server Reporting Services 2008 it is important that you check whether your operating system is supported ( Windows Server 2003,2008,2012, some editions of XP, Vista, 7 and 8), you have sufficient RAM ( 512mb free), your CPU is powerful enough ( 1.0GHZ ) and if you have sufficient hard disk ( about 2gb). The installation checks will check all of this but a bit of prior research could prevent any time being wasted.

Always refer to Book Online to check the required specifications as these are the most up to date available on the internet.

1. Goto Start Menu > All Programs > Microsoft SQL Server 2008 R2, SQL Server Installation Centre


2. Click on New Installation or add new features to an existing installation


3. The SQL server setup wizard will then check your environment to see whether it meets the requirement to install reporting services. As long as there are no failures click OK.


4. As long as there are no problems click Install on the next screen.


5. The setup rules will check the system against another set of rules and will highlight any problems.


6. Select Full Text Search Reporting Services , Business Intelligence Development Studio, Client Tools Connectivity, Management Tools – both Complete and Basic.


7. Another check will be done against the features selected. Press next if there are not any problems.


8. Disk space of your setup will be checked.


9. Please use whatever accounts you have assigned for the account. I will be using the local service account as I haven’t properly setup my VM.


10. Press next


11. Press next. Generally we don’t send back the rror reports.


12. The final checks will be done and press next.


13. There will be a comprehensive list of the options chosen. Please review this list and then if your happy press install.


14. When the installation is completed successfully then you will get the final screen.


Easily Check Database Integrity (DBCC CHECKDB) For All Databases On A Server

Today I was doing my daily checks on one of my biggest SQL instances which has over 1,000 databases and noticed that SQL Agent was stopped. I couldn’t see any errors and was able to simply start it without any problems but I was concerned that the daily integrity check jobs had not run and I am way too cautious to get caught by not doing it manually.

Initially I wasn’t  too sure how to do it very quickly without creating a script using DBCC CHECKDB, querying sys.databases  with some dynamic T-SQL but decided to give the below script a try and luckily it worked.

 EXEC sp_MSforeachDB ‘DBCC CHECKDB (?)’

What are the SQL Server System databases?

SQL Server System databases

SQL Server has 5 very default system databases which are

  1. Master
  2. MSDB
  3. Model
  4. TempDB
  5. Resource


As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.


The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.


This is basically a template database for the creation of new databases.


TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..


This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.

What is Business Intelligence ( BI )? Business Intelligence Development Studio ( BIDS )? SQL Server Reporting Services ( SSRS )? SQL Server Integration Services ( SSIS )? SQL Server Analysis Services ( SSAS )?

What is Business Intelligence ( BI )? Business Intelligence Development Studio ( BIDS )? SQL Server Reporting Services ( SSRS )? SQL Server Integration Services ( SSIS )? SQL Server Analysis Services ( SSAS )?

What is Business Intelligence ( BI )?

Business intelligence is simply using software to manage large amounts of data to get meaningful and actionable results. It is also an growing field within IT where people are choosing to specialise in.

Software associated with BI includes SQL Server Reporting Services, SQL Server Analysis Services, Biztalk, Business Objects, Cognos…..

Business intelligence is used to make smarter marketing, identify new markets, efficiency and cost savings.

What is Business Intelligence Development Studio ( BIDS )?

Business Intelligence Development Studio ( BIDS ) is a custom version of Visual Studio which is supplied with SQL Server where the development of Analysis Services databases and mining models, Integration Services packages and Reporting Services reports is completed.

If BIDS is installed on a system that already has a version Visual Studio installed then these additional project types are added to the existing installed Visual Studio.

What is SQL Server Reporting Services ( SSRS )?

SQL Server Reporting Services is a comprehensive reporting feature of SQL Server where reports can be created, deployed and managed across an Enterprise scale environment.

SSRS is used by many firms to replace Crystal Reports, Excel and Access as it has a far greater range of abilities and features as well as far greater performance.

What is SQL Server Integration Services ( SSIS )?

SQL Server Integration Services is often described as an ETL tool as its function is to Extract, Transform and Load data (ETL). It is often used to perform repetitive tasks as each process can be saved as a SSIS package and can be scheduled to run automatically.

SSIS packages can be made simply via the GUI, BIDS ( drag and drop ) or enhanced using T-SQL.

What is SQL Server Analysis Services ( SSAS )?

SQL Server Analysis Services is made up of two distinct part which an Online Analytical Processing (OLAP) and Data mining.

OLAP allows the building of multidimensional structures which are known as cubes. Using these structures businesses can then analyse the data to solve problems. Cubes are queried using Multidimensional Expressions (MDX) rather than T-SQL.

Data mining allows the analysis of huge amounts of data and is used to find patterns and insight into information which is not as seemingly obvious as that found in Cubes.

How can I find out what SQL Server version is running?

I was asked by someone which SQL Server version a server was running and was surprised when my DBA colleague said to run a stored procedure I had never heard of.  I had not thought that there would be more than one way as the usual method I used below was so simple I didn’t think that there would be a need for another method.  I then did a deep dive of all the different ways I could find out the version of SQL Server I was running and have included them below.

Method 1

Go to Help > About


Method 2

Use the following script .



That is the script which I would use and I think is pretty much the most commonly used method within the SQL Server community.

Method 3

EXEC sp_MSgetversion


Method 4

EXEC xp_msver


Method 5

SELECT SERVERPROPERTY(‘productversion‘), SERVERPROPERTY (‘productlevel‘), SERVERPROPERTY (‘edition‘)


Method 6

Within the SQL logs the current version will be listed when the log was last cycled.