Share via

Successfully execute an INSERT, UPDATE and DELETE against a Database Snapshot

  • Article

Author: Shaun Tinline-Jones

Reviewers: Mike Ruthruff, Sanjay Mishra, Alexei Khalyako

Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively easy answer, thanks to the Database Snapshot feature. I was however surprised at the next question “Can we update the database snapshot?”

A reactive response is “No. You cannot update a Database Snapshot”

 Msg 3906, Level 16, State 1, Line 1
 Failed to update database "Orig_Snapshot" because the database is read-only.

A creative answer is a tentative “….well maybe…depending on what the objective is?”

This blog demonstrates that it is possible to run an INSERT, UPDATE or DELETE against a Database Snapshot.  This will not update the snapshot, but rather the database that has a "Database Snapshot" associated to it.

Imagine a scenario where a reconciliation of data at a point in time must be carried out. Database Snapshot provides the ability to present the data as at a point in time, however the common understanding is that any compensating modifications requires a second connection or a USE statement. The USE statement is not permitted in a database module:

 Msg 154, Level 15, State 1, Procedure testSP, Line 4
a USE database statement is not allowed in a procedure, function or trigger.

Listing 1 creates the objects needed to prove it is possible to successfully execute DML statements against a Database Snapshot.

     IF DB_ID('Orig') IS NOT NULL
        DROP DATABASE [Orig]
    GO
    
    CREATE DATABASE [Orig]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig.mdf')
    GO
    USE [Orig]
    GO
  
    IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
        DROP TABLE dbo.TestTable
    GO
    
    CREATE TABLE dbo.TestTable (Col1 int)
    GO
    
    IF OBJECT_ID('dbo.UpdView', 'V') IS NOT NULL
        DROP VIEW dbo.UpdView
    GO
    
    CREATE VIEW dbo.UpdView
    AS
    SELECT Col1 FROM Orig.dbo.TestTable
    GO
    
    INSERT INTO dbo.TestTable (Col1) VALUES (1)
    GO
    
    IF DB_ID('Orig_Snapshot') IS NOT NULL
        DROP DATABASE [Orig_Snapshot]
    GO
    
    CREATE DATABASE [Orig_Snapshot]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig_Snapshot.ss')
     AS SNAPSHOT OF [Orig]
    GO
    
    USE [Orig_Snapshot]
    GO
    
    SELECT * FROM dbo.UpdView
    GO

After running the above code, you should have a database and an accompanying snapshot of that database. Within the database, you’ll have a table with at least 1 row in it and a view that simply returns the contents of that table.  This view is a fully qualified name of the original database, this creates 2 scenarios.  One is that it becomes possible to view the originating database from within the snapshot, and the second is the ability to run DML statements against the source database from within the database snapshot.

Listing 2

     INSERT INTO dbo.UpdView VALUES (2), (3);
    UPDATE dbo.UpdView SET Col1 = 99 WHERE Col1 = 3;
    DELETE FROM dbo.UpdView WHERE Col1 = 1;
    SELECT * FROM dbo.UpdView
    GO

Running the code in listing 2, you should have a result set as shown below:

 

In conclusion, the error message that informs us the database is read-only, while identical to the message returned when writing to a Read-Only database, is only partially accurate in the context of a Database Snapshot. Questions that come to mind are:

  • Is it a bug?
  • Would this be considered a good practice?
  • What does the code management look like?
  • What useful scenarios could leverage this insight?

Let me know if you think it’s a bug. From my perspective, this makes sense as the database snapshot is actually a read/write database, persisting older values as records change. Additionally, we are not actually updating the Database Snapshot, the changes are still made directly to the main database.

As far as been a good or recommended practice, my reservations about using this in a design are:

  • Views must be created for each table that can be updated from the database snapshot
  • Separate objects/statements must be created for DML operations that already exists for the main table
  • It isn’t intuitive in terms of troubleshooting code. In fact, if you start down this road you may end up with one of those horrendous applications that have layers upon layers of views ultimately leading to poor performance.

It does however reduce the cost of creating and managing a new connection, especially if the DML statements are a result of a query that originated from the snapshot. Additionally, the logic could reside in the same SP as the query, allowing for conditional logic.

Can you think of other scenarios, pitfalls or benefits of updating the main database via a database snapshot?

Comments

  • Anonymous
    October 26, 2011
    The comment has been removed
  • Anonymous
    October 29, 2011
    The proposed solution is interesting. However, it's more like a wrapper to the original database. A much simpler, and developmentally efficient solution would be to use 2 connection strings:
  1. String #1 - for read-only operations, connecting to the snapshot
  2. String #2 - for insert/update/delete, connecting to the original database

  • Anonymous
    October 31, 2011
    Whilst the article title sounds interesting, it sounds like a design workaround and snapshot itself is not really updatable. Dont see this as an issue, more like a design thingy. Snapshot stays as it is, but the views itself points to the normal user database and hence no updateinsertdelete error. Why not.

  • Anonymous
    November 07, 2011
    The comment has been removed

  • Anonymous
    November 15, 2011
    The comment has been removed

  • Anonymous
    December 20, 2011
    How does this affect mirrored databases? Regards Perry

  • Anonymous
    July 08, 2013
    The comment has been removed

  • Anonymous
    July 08, 2013
    I think its a bug. Cause as a DBA if i allow a user to insert,update or delete to a snapshot i will definitely want to keep those operations isolated from original database .Which is not happening in this case and its too dangerous for the original DB for those users who might run into something like this unknowingly thinking that a such DML is local to the snapshot.  

  • Anonymous
    July 08, 2013
    Very clearly explained..... bravo!!!

  • Anonymous
    August 16, 2013
    One useful scenario comes to mind, not necessarily using snapshots but read-only databases. Essentially a snapshot is a read-only database made at a point in time. In this scenario, you could have a read-only database exposed to your applications, with views and stored procedures referencing your updateable database. When it is time for a design change, simply publish a new read-only database containing new version. You could even leave old and new versions of read-only database side by side to support multiple versions of your app simultaneously. This of course depends on the extent of changes being made to the writable database, but it is possible. In fact, this sounds similar to SQL Server master "resource database".

  • Anonymous
    October 15, 2013
    Very good. Tks for sharing

  • Anonymous
    October 16, 2013
    Very interesting. Let's test, tks.

  • Anonymous
    August 11, 2015
    This doesn't seem to work anymore. Am using SQL Server 2014. I really needed a solution like this with a snapshot that needed to have minor updates when errors were found. Didn't want to go down the AutoAudit option. This seemed like a simple solution