Tag: DB Recovery

  • Steps to restore Database using Database Snapshot

    Problem: Take a scenario where vendor wants you to up-grade the vendor databae using the SQL scripts. In case if something goes wrong you can either recover database by restoring all backup or by creating database snapshot.

    Solution:
    Database snapshots feature is available from the SQL Server 2005. Snapshot will be used to create a read-only copy of a database at a given point in time. Any transactions which are uncommitted at the time you create a snapshot will not be included in the database snapshot. You can create multiple snapshots of a source database, but those snapshots must reside on the same instance as the source database.

    You must have SQL Server enterprise edition to create the snapshot. A database snapshot only contains the data pages that have changed in the source database since the snapshot was created. It contains the original copies of those pages in order to give the effect of a read-only view. The file that is created to hold the changed data pages when the snapshot is created is known as a sparse file.

    A source database that contains a snapshot cannot be dropped, detached, or restored until all of the snapshots have been dropped.

    Check below queries to restore the database using snapshot
    Before you start restoring using database snapshot
    — Make sure the source database does not contain read-only or compressed filegroups.
    –Make sure all the files are online that were online when the snapshot was created.
    –Delete all snapshots of the source database, except the one you are reverting to.

    create database db_pool
use db_pool

create table tb1
(
	id int,
	name varchar(10)
)

insert into tb1 values(10,'Jugal')
go 50;

--select the database and execute the below query to get logical name
SELECT name FROM sys.database_files WHERE type <> 1

--execute below command to create the database snapshot
CREATE DATABASE db_pool_Snapshot
 ON
(Name = db_pool,
 FileName = 'C:\db_pool_Data.ss')
 AS SNAPSHOT OF db_pool
 
--select the original database and drop the rows
use db_pool
delete from tb1

--use the snapshot and query tb1, we can see the data evenif it is deleted 
 use db_pool_Snapshot
 select * from tb1
 
 --now restore database using below script from snapshot
USE master
GO
RESTORE DATABASE db_pool FROM DATABASE_SNAPSHOT = 'db_pool_Snapshot'
 
 --query the database to check changes
 use db_pool
 select * from tb1