Cannot BACKUP or RESTORE a database snapshot

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


Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Cannot BACKUP or RESTORE a database snapshot | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.