I’ve been going through the process of upgrading from SQL Server 2012 to SQL Server 2014 and I’ve hit a bug when running snapshots. Any snapshot that was in place from SQL Server 2012 cannot be dropped in SQL Server 2014. There’s a Connect article already open.
Now before you all go thinking that I just ran the installer and to hell with the consequences, I did plenty of reading around upgrading via MSDN. There is also no mention of redundant snapshots post-upgrade being a limitation on snapshots on MSDN. I ran the Upgrade Advisor and there was no mention of snapshots being a potential issue. I re-ran on another instance with a snapshot running and have provided a screenshot below.
So mistakes happen, it’s a bug, the upgrade task should check for snapshots before upgrading takes place and warn or error right? Well what got me mad was the response by Microsoft:
This is expected behavior, and database snapshots are not expected to be work after an upgrade.Oh really? You’ll allow a user to upgrade the software even though it means that we’re left with a redundant snapshot? Nonsense. If it’s intended behaviour why is there no check for it? It’s an oversight that really ought to be resolved before too many people get caught by it. And that’s not the worse of it; because there is a snapshot you cannot drop then you can’t drop/detach/restore the original database, so you’re stuck with it.
A lot of good things are coming with SQL Server 2014, such as clustered columnstore indexes, but this oversight and subsequent response is lousy, and I hope that Microsoft fix the bug in an upcoming CU.