Database Snapshot

Leave a comment

How do you make a SQL Server database snapshot? Have to use T-SQL. Here’s the example from MSDN that will work with the AdventureWorks sample database.

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\’ )
AS SNAPSHOT OF AdventureWorks;

You can’t specify a log, size or growth parameters, since snapshots are read-only.

Once you run this, a Database Snapshot folder appears in SQL Server Management Studio. You can reference the database with the use command.

The database snapshot just stores changes made after the snapshot was taken, so the file stays small relative to the original database file. And following that concept, users querying against the snapshot will still hit the original database, so you don’t relieve utilization there.

Here’s a nice link summarizing more:,289483,sid87_gci1176142,00.html

And what about those other SQL Server snapshots? There is snapshot replication, which is one time read-only replication that is transmitting a physical copy of the data. And there are report snapshots in Reporting Services, which is a cached complete copy of a report.

Reporting Services Class 2793A: Day Two

Leave a comment

Started off doing Report Models, which is the basis for making easy to use end-user report building scenarios. You make the Report Model in the Business Intelligence Studio (BIDS), then end user can manipulate the Report Models using Report Builder.

The instructor mentioned a 3rd party company, Dundas ( that makes Reporting Services add-ins for slick looking charts, guages, maps, calendars, etc.

Report Models allow one to reverse the physical data model tables into objects along the lines of entities, attributes. Further folders and perspectives are views to these entities. When creating a report model, a wizard has check boxes with entries like “Create an entity for each table”, “Create an attribute for each column”, “Create aggregate functions for each numeric column”.

Report Builder is a Windows Forms application that gets installed automatically to the users workstation after clicking once on a hyperlink. ReportBuilder definitely was simpler than doing reports within BIDS, but I bet it might still prove difficult for some users unless they are familiar with the data that they are using. The link to access Report Builder is on the Reporting Services homepage.

Report Manager is the web based application where you can access reports, but also manipulate some aspects of report definitions, such as the title, data source, parameter values but not definitions and a few other things. It is usually located at http://[hostname]/reports. There are a variety of options for execution of reports within Report Manager governing when the report runs against the most recent data. For example, you can set a threshold of when a cached report should expire, forcing a run against the database rather than against cached data. You can also specify that a report always execute against snapshot data. Using stored snapshots requires a bit of administration, since credentials for re-executing snapshots must be stored within the database. This can all be done with Report Manager.

Report Manager has site-wide settings that regulate things like the title of Report Manager, how many total snapshots can be stored, etc.

Report Manager also administers security for accessing reports and subscriptions to reports so that users get reports pushed to them via email. Data driven subscriptions enable you to store parameter values in the database so that the subscription output can vary according to values stored in database tables.

Key SSRS configuration files: RSReportServer (data connection strings, SMTP server, render and data source extensions), RSWebApplication (delivery extensions and active user request limits), RepportingServicesService (trace level and log files) and RSReportDesigner (rendering, data source and designer extensions). The first 3 are found in subdirs within the Reporting Services installation folder. The 4th one is in the Visual Studio IDE\Private assemblies directory.

Administering overall configuration of Reporting Services is done with the Reporting Services Configuration tool, which is different than Report Manager. This configuration tool addresses how many hosts comprise the reporting services deployment, site-wide encryption keys, starting and stopping the service, what account runs the service, the location of the databases for Reporting services, etc.

There are trace files for debugging. There are 5 levels of tracing, starting from none to verbose mode. In between are combinations of exceptions, restarts, warnings and status. These are all stored in the [instance]\Reportingservices\Logfiles directory. You need to trim trace logs manually.

There are also log tables within the ReportServer database. Data goes into the ExecutionLog table. It’s hard to view, so there is an Ifornamtion services package called RSExecutionLog_update.dtsx to export elsewhere for easier viewing.

Finally, there are performance counters that allow you to monitor the windows service and web service.

Back up those encryption keys! Without them, you are hosed…And of course, back up the ReportServer and ReportServerTempDB. How big will databases get? Depends upon the total number of reports, the intermediate report size, whether intermediate reports persist and how much report caching happens on the site.