Welcome to Julian Kuiters Saturday, August 18 2018 @ 05:09 PM AEST

Cannot BACKUP or RESTORE a database snapshot

  • Contributed by:
SQL Server 2005

I've been beta testing SQL Backup 5 and came across an old problem. If you iterate through all the databases on a server and issue a BACKUP DATABASE for Database Snapshot you will receive an error message similar to:

Msg 3002, Level 16, State 1, Line 1
Cannot BACKUP or RESTORE a database snapshot.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

You may also see in your server logs:

Msg 3041, Severity 16, State 1.
BACKUP failed to complete the command BACKUP DATABASE databasename. Check the backup application log for detailed message.

Snapshot databases should not need backing up, as they are read-only representations of another database. Amongst the limitations of Database Snapshots are that you cannot backup or restore a snapshot database. Instead if you need, you can revert the original database back to the Database Snapshot version. Make sure you backup the original database first!

How do you know if a database is a snapshot?

Snapshot databases will reference their original database using the source_database_id column in sys.databases. The following SQL displays which databases are real and which are snapshots:

-- real databases on the server
SELECT name FROM sys.databases WHERE source_database_id IS NULL

-- snapshot databases on the server
SELECT name FROM sys.databases WHERE source_database_id IS NOT NULL