question

Katie 1 avatar image
Katie 1 asked

Restore a database snapshot on another server

hello

there is a snapshot of a production SQL SERVER 2005 database taken last week and we need to restore the snapshot onto another server altogether.the user is requesting for the snapshot that was taken lastweek to be restored. Is there any suggestion as to how to do it? Any work around is

Any suggestions will be immensely appreciated.

Thanks.

sql-server-2005administrationdatabase-snapshot
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

· Write an Answer
Blackhawk-17 avatar image
Blackhawk-17 answered

Snapshots are "linked" to a database. They are more logical constructs than actual dBs. As such they can not be backed up (or restored). They retain the orginal data from extents that change after the snapshot creation.

If your dB isn't too large you can do a series of SELECT->INSERTS from the snapshot to a new dB and backup/restore that.

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image
Ian Roke answered

I am not familiar with snapshot replication but a quick search brought up Backing Up and Restoring Replicated Databases which I hope will give you some pointers.

2 comments
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Katie 1 avatar image Katie 1 ·
Ian, this is not exactly the replication concept of SQL SERVER but the database snapshots : to give you an exact idea here is the link http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/
0 Likes 0 ·
Ian Roke avatar image Ian Roke ·
Sorry I stand corrected. Had a look at that article and it looks like something I need to do some more learning on so if you are still struggling I will post back my findings soon. Sorry I couldn't be of more help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.