Can we access snapshot database of a log shipping database while we are restoring log files

Question

I have a question regarding snapshot database of a log shipping database.

I have a database name like Northwind on server A and I am shipping it to server B. On server B, the log shipped copy of the database is restored WITH STANDBY so that it can be queried. I want to create and access only snapshot database of the Northwind_standby database, so that I can query the snapshot while logs are restoring.

Q1. 1s it possible to access the snapshot database while restoring logs? or will we get binding errors?
Q2. If we get binding errors why is that happening as we are not accessing the actual database but we are accessing the PIT snapshot of Northwind?
Q3. As snapshots are linked to source database id in sys.databases still is it going to read large data from actual database only?

Any inputs would be appreciated or let me know if I have to more specific with my architecture and question.

Answer 1

No, you cannot do this.

You can create a snapshot database of a log shipped database that is in STANDBY mode. However, in order to restore additional logs, you will need to drop the snapshot.

Here's a quick test you can do to simulate this on a development machine:

Step 1: Create a database & make sure it's in full recovery.

CREATE DATABASE Northwind;
ALTER DATABASE Northwind SET RECOVERY FULL;

Step 2: Take a full backup & TWO log backup

BACKUP DATABASE Northwind TO DISK = 'C:\MSSQL\Backup\Northwind.full.bak' WITH INIT;
BACKUP LOG      Northwind TO DISK = 'C:\MSSQL\Backup\Northwind.log1.bak' WITH INIT;
BACKUP LOG      Northwind TO DISK = 'C:\MSSQL\Backup\Northwind.log2.bak' WITH INIT;

Step 3: Restore the full and first log backup. Put the log shipped copy into STANDBY so it's read-only

RESTORE DATABASE Northwind_Standby 
    FROM DISK = 'C:\MSSQL\Backup\Northwind.full.bak'
    WITH
        MOVE 'Northwind' TO 'C:\MSSQL\Data\Northwind_standby.mdf',
        MOVE 'Northwind_log' TO 'C:\MSSQL\Data\Northwind_standby_log.ldf',
        NORECOVERY;
RESTORE LOG      Northwind_Standby 
    FROM DISK = 'C:\MSSQL\Backup\Northwind.log1.bak'
    WITH NORECOVERY;
RESTORE DATABASE Northwind_Standby
    WITH STANDBY = 'C:\MSSQL\Data\Northwind_standby.standby';

Step 4: Create a snapshot of the restored/standby database

CREATE DATABASE Northwind_Snapshot 
    ON (NAME = 'Northwind', 
    FILENAME = 'C:\MSSQL\Data\Northwind_standby.snapshot' )  
    AS SNAPSHOT OF Northwind_Standby;  

Step 5: now try to restore the second log

RESTORE LOG      Northwind_Standby 
    FROM DISK = 'C:\MSSQL\Backup\Northwind.log2.bak'
    WITH NORECOVERY;

When you try to restore another log, you'll get this error:

Msg 5094, Level 16, State 2, Line 32
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 32
RESTORE LOG is terminating abnormally.

The only way you'll be able to continue restoring logs is to drop all existing snapshots. So you cannot query a snapshot while log backups are being restored.