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
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