1

I know the logic behind database snapshot and how it works. But my problem is how can database snapshot to be used to recover data? can we take a backup of database snapshot and restore it in a new server?

I am using MS SQL server

Thank you.

4

how can database snapshot to be used to recover data?

Yes, you have to use restore database ... FROM DATABASE_SNAPSHOT. It is called reverting the database to a database snapshot. You would use it to reverse a serious error e.g. drop table, deleting data, etc.

This is important :

all changes made after the snapshot was created are lost.

.

can we take a backup of database snapshot and restore it in a new server?

No, you cannot. This is covered in Database Snapshot limitations

  • You cannot back up or restore database snapshots.

  • You cannot attach or detach database snapshots.

Very important :

Using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

3

But my problem is how can database snapshot to be used to recover data?

You have two options here.

  • You can revert your existing database back to the point of the snapshot using this command:

    RESTORE DATABASE DatabaseName FROM DATABASE_SNAPSHOT = SnapshotName

    However that will overwrite your existing database. If you think you might need to keep the changes you are overwriting you should take a backup of your current database before reverting to the snapshot.

    Also remember that only the snapshot you are reverting to can exist. So while you can create multiple snapshots at various points in time you have to drop all of them but one before you revert to that snapshot.

  • This is a bit of an overkill if you are just trying to recover a few rows lost due to an accidental delete. Remember that the snapshot is a read only copy of the database at the time you took the snapshot. So you can easily select out the rows you need and add them back into your main database.

can we take a backup of database snapshot and restore it in a new server?

You can't backup a snapshot. You are much better off using your existing recovery strategy, that presumably includes regular backups, and restore one of those backups to the alternate location. However if for whatever reason this isn't an option and you NEED to copy that data you should again remember that it is a read only copy of the database. You can use a tool like SSIS to copy the data to another location.

2

There is another way in which snapshots can be used to recover lost data, specifically when used with AlwaysOn Availability Groups.

If there would be a need to have a forced failover, a snapshot can be created of the database(s) that were previously the primary before resuming data movement. This would give you a before and after picture (snapshot is before, current db is after) of the forced failover and an potential data loss that would be a result of the forced failover.

This was not meant to be a solution to the OPs question which was not specifically about AGs, but it is a way to use database snapshots for data recovery that was not mentioned in any other answer.

Feel free to incorporate this text into a different answer and then delete this answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.