Cannot BACKUP or RESTORE a database snapshot
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:
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:
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:
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
