Home / server / Questions / 288948
In Process
Ian Ringrose
Asked: 2011-07-12 02:00:15 +0800 CST

How do I backup a sql server 2008 database snapshot?

  • 772

How do I backup a database snapshot, so I can restore it as another database (on same or different server?)

We do not wish to roll back to the snapshot, as we need both versions of the database active for testing upgrade scripts.

backup sql-server-2008

2 Answers

  1. I don't believe you can directly backup a database snapshot, and I can't remember if this is a planned feature for the upcoming new version.

    If you don't mind a few extra steps, you can do this:

    1. Make a backup of the live database.
    2. Restore the live database using the snapshot in question.
    3. Make another backup of the live database (which will now match the snapshot).
    4. Restore the live database using the backup made in step 1, to get you back to before you restored from the snapshot.

    It's a bit messy, and there will be some down-time for the database, however.

    • 1

  2. Another option that might get you what you need is to use a data comparison tool such as Sql Server Data Tools or Redgate Sql Compare.

    Then, as long as you can get the comparison tool hooked to both database servers at the same time, you can simply use the snapshot as your source database and sync the data to your target server.

    • 0