How to restore database using snapshot in sql server

Snapshot database has been added as alternative backup option in sql server 2005. Using snapshot you can create database backup that will help in restoring  if any failure occurs.

Snapshot database is a static and read only view of source database. you can create multiple Snapshot for the same database, all can exists on the same server.

Benefits of snapshot database 

1. Easy to create without any impact on production server.

CREATE DATABASE SnapshotDB ON
(Name =’testdb’,
FileName=’d:\testdb_snapshot.ss’)
AS SNAPSHOT OF testdb;

2. Restoration can be performed by snapshot database backup as quickly as possible.

Restore database testdb
from Database_Snapshot = ‘SnapshotDB’

3. Snapshot database can be used as reporting or testing database.

Select * from snapshotdb.dbo.empdetails

lets go through with an example to explain the working of snapshot database.

Lets say due to administrative error or user error table got dropped from the production server. Now to restore database back to the time when table got deleted its possible if the database snapshot was created before that point.

Reverting database snapshot is much faster compare to the backup restoration. We can also manually reconstruct a dropped table or other lost data from the information in a snapshot.

For instance, we could bulk copy the data out of the snapshot into the database and manually merge the data back into the database.

Things to remember while restoring database snapshot

1. Before restoring snapshot, make sure that no one is connect to snapshot database otherwise it throw below error saying snapshot database in use.

Msg 5070, Level 16, State 2, Line 2
Database state cannot be changed while other users are using the database ‘SnapshotDB_20092011’
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

2. Also make sure that restoring database should be in single user mode while restoring snapshot database to avoid any issue.
Use the below script to bring back database in single user mode.

alter database testdb
set single_user
with rollback immediate

Now execute the below script to restore snapshot database.

If exists (select dbid FROM master.dbo.sysdatabases
WHERE name=’snapshot20092011′)
Drop database snapshot20092011

USE master
Restore database testdb
from Database_Snapshot = ‘snapshot20092011’

 

Leave a Reply

  

  

  


7 − 3 =