Database snapshots are not accessible after an In-place upgrade

I worked on an issue where database snapshots were inaccessible after performing an In-place upgrade of SQL Server 2005 instance which has database snapshots to SQL Server 2008 R2. Upgrade completed successfully and all the SQL Server databases are accessible without any issues, however got the following error when I tried to access the database snapshots.

************************************************************************************************************************************************

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot open database ‘Test_db_snapshot’ version 611. Upgrade the database to the latest version. (Microsoft SQL Server, Error: 946)

************************************************************************************************************************************************

Database Snapshots (http://msdn2.microsoft.com/en-us/library/ms175158.aspx) in SQL Server 2005 or later Enterprise Edition allow a read-only point-in-time view of data.

To understand this behavior, I had installed SQL Server 2005 and created a database snapshot. I then ran the upgrade to SQL Server 2008 R2 which completed successfully, after which I got the same error when I tried to access the snapshot database.

As the issue occurs after upgrading the SQL Server 2005 instance to SQL Server 2008 R2, I planned to run the SQL Server 2008 R2 upgrade advisor to test if it detects the issue.

I have installed new SQL Server 2005 instance and created a database snapshot. I then Installed and ran the SQL Server 2008 R2 upgrade advisor which generated an upgrade advisor report. From the report I could notice an error which says “Read-only databases cannot be upgraded” and under that when I clicked on “Show affected objects” got a pop up listing “Test_db_snapshot”.

Screenshot of the error generated by SQL Server 2008 R2 upgrade advisor.

SQLServer2008R2UpgradeAdvisor

SQLServer2008R2UpgradeAdvisor


SQL Server 2008 R2 Upgrade should be able to modify any of the existing objects on the instance while it is upgrading, but a database snapshot is a read-only, static view of a database (the source database), So the upgrade cannot make any changes to this database snapshot.
Before upgrading SQL Server instance, it is recommended to run the upgrade advisor to find out the objects that will get affected by upgrade and which need to be fixed before running the upgrade.

The report of the upgrade advisor will list out the issues & objects that will be affected by the upgrade from where we can find that “read-only databases cannot be upgraded” and when we click on “show affected objects”. We can find the database snapshots listed over there.
Delete database snapshots before upgrading the SQL Server 2005 to higher versions.

Happy Reading,
SQLServerF1 Team

 

Leave a Reply

Your email address will not be published. Required fields are marked *