SQL with Manoj

 
Today while restoring a Database and Dropping it I came across following errors:
 

Error #1. The first error was while I was trying to RESTORE an existing database from a backup file:

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

Error #2. The second error came while I tried to DROP that existing database, to ignore the previous error:

Cannot drop the database while the database snapshot “dbStaging_ss_20160607” refers to it.
Drop that database first. (Microsoft SQL Server, Error: 3709)
 

By both the above errors its obvious that a Database Snapshot is associated with the Database that I’m going to Restore or Drop. So first try to identify and DROP that Snapshot. You can do this by checking in SSMS under Object Explorer –> Databases –> Database Snapshot, or running the following query:

USE [master]
GO

SELECT * 
FROM sys.databases
WHERE source_database_id IS NOT NULL
GO

The above query will list all the Snapshots created for existing Databases. Just check the Snapshot that is associated with the Database you were trying to Restore.
 

Before dropping the respective Snapshot, script out the DDL to create the Database Snapshot back, by Right Clicking on the Database Snapshot –> Script database as –> CREATE To –> Clipboard. The script will look like below DDL Script:

USE [master]
GO

CREATE DATABASE [dbStaging_ss_20160607] 
ON ( 
	NAME = N'dbStagingDB', 
	FILENAME = N'E:\dbStaging\dbStaging_Primary.mdf' 
) 
AS SNAPSHOT OF [dbStagingDB]
GO

After you are done by scripting the Database Snapshot DDL, just DROP the Database Snapshot, just like you drop any other Database:

USE [master]
GO

DROP DATABASE [dbStaging_ss_20160607]
GO

After you are done with this, try Restoring back the Database that you were trying initially.