So we have our mirrored database all setup and its sat in a RECOVERING state, so how can we use it as reporting solution? Well, the answer lies in another feature new to SQL 2005… Database Snapshots.
You can’t run queries against your mirrored DB because it is a in a RECOVERING state but you can create a snapshot of your mirrored database and run queries against that…here’s how:
You need to get the logical file names of the data files that make up the mirrored database, these will be the same for both databases, to get these I run the following script against the principal database:
use MirrorDBtest Go select * from sysfiles
The result of this query is:
So with the logical name of my data file
I run the following CREATE DATABASE statement to create a snapshot on the mirror database:
CREATE DATABASE MirrorDBTestSS ON (NAME = 'MirrorDBtest', FILENAME = 'C:\DATA\SQLServer2008\MirrorDBTest_mirror.snp') AS SNAPSHOT OF MirrorDBtest
In the above script I specifying the location of the snapshot file on the secondary instance
Once this script has been run you should have a fully fledged database snapshot created against the mirrored database.
You can then run queries against the snapshot:
Part 1 - Configuring Database Mirroring in SQL Server.