The operation cannot be performed on a database with database snapshots or active DBCC replicas

 

When trying to restore the database with snapshots got the following error SQL message

SQL Server T-SQL Command

restore database PRD_Accounts from disk ='H:\PRD_Accounts_backup_daily.bak' with replace, stats

Error:

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.

Resolution

Since we cannot restore a database if we have any snapshots.
Please drop all snapshots and try database restore again.

1. To check the database snapshot and it’s source database

select database_id, name,source_database_id,Source_DBName=DB_NAME(source_database_id) from master.sys.databases where source_database_id IS NOT NULL

2. Drop the Snapshot database

DROP DATABASE Accounts_Rpt

3. Create script of snapshot database, before dropping it. If at all we need to re-establish the snapshot after restore we can use it

USE master GO /****** Object:  Database [Benefits_Rpt_sol48u]    Script Date: 12/03/2012 00:01:02 ******/ CREATE DATABASE [Accounts_Rpt] ON ( NAME = N'PRD_Accounts', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss1' ), ( NAME = N'indexdat1', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss2' ) AS SNAPSHOT OF [PRD_Accounts] GO

How to find the status of Backup / Restore in SQL Server 2000

To know the status of backup or restore in SQL Server 2000 ;

1. Find the SPID which is restoring the DB using sp_who2 or from sysprocesses

2. Execute dbcc outputbuffer(SPID)

From the output get the %completed status: