Cannot restore SQL Server snapshot

Question

For some reason I cannot restore my SQL Server snapshot.

I have the a database DB01 with a snapshot DB01_SS.

When trying to restore the snapshot with the following query:

USE master
GO

ALTER DATABASE DB01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO

RESTORE DATABASE DB01 FROM DATABASE_SNAPSHOT = 'DB01_SS';
GO

ALTER DATABASE DB01 SET MULTI_USER
GO

I get this error message:

Msg 3137, Level 16, State 1, Line 5
Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

I have checked, the name is properly specified. There are no other snapshots available on the server. The only strange thing I came across was when I logged on the SQL Server to look up the snapshot file. And it had a different name then in the SSMS gui. It was named DB_SS_2017.SS. When trying to restore with that name, I get the following error:

Msg 911, Level 16, State 4, Line 5
Database DB_SS_2017.SS does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

And last but not least, when trying to check the properties of the snapshot, again in SSMS. The properties button is greyed out! How can I restore the SQL Server snapshot?

Answer 1

You are may be in one of the following cases:

1. You have multiple snapshots defined, try refreshing the snapshots folder in ssms or run select * from sys.databases where source_database_id IS NOT NULLto see if you have more snapshots. Because snapshots are 'copy on write', you cannot restore if you have multiple snapshots for a single database.

2. You are not specifying correctly the logical name of the snapshot. Run select * from sys.databases where source_database_id IS NOT NULL to check the the logical name of the snapshot. Then rerun the restore using that name.

3. Your snapshot file is not linked anymore with the snapshot (snapshot got somehow corrupted). The statement below queries the pages in the snapshot file. Run it and see if you have any errors. select db_name(database_id) [<database name>], database_id, count(*) from sys.dm_os_buffer_descriptors where database_id= (select top 1 database_id from sys.databases where source_database_id IS NOT NULL) group by database_id