What are you looking for ?
Home » » Database Roll Back

Database Roll Back

Reverting your SQL Server database back to a specific point in time.
There are certain circumstances in which you may need to roll back your database to a specific point in time. There may be various reasons why this could be necessary but it is usually related to the execution of accidental or malicious DML or DDL statements. For example:
  • Someone executed an update without a WHERE clause
  • Someone accidently dropped a table
  • Your attempted database upgrade failed, and left the database in an unknown state

Database Recovery

SQL Server provides two mechanisms for you to recover from these events:
  • SQL Server Database snapshots
  • Point-in-time restore

SQL Server Database snapshots

A database snapshot is a view of what the source database looked like at the time at which the snapshot was created. This means that all the objects will be the same as what it was when the snapshot was taken and all of the data will be exactly as it was then

To use database snapshots to recover from an unwanted DML statement, you need to have a suitable snapshot in place

Step 1 - Create the Database
CREATE DATABASE [Snapshot_DB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Snapshot_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Snapshot_DB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Snapshot_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Snapshot_DB_log.ldf' , SIZE = 504KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
Create table for testing 
USE [Snapshot_DB]
GO
CREATE TABLE [dbo].[Products](
       [ProductID] [int] NULL,
       [Name] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Products] ([ProductID], [Name]) VALUES (1, N'AA')
GO
INSERT [dbo].[Products] ([ProductID], [Name]) VALUES (2, N'BB')
GO
INSERT [dbo].[Products] ([ProductID], [Name]) VALUES (3, N'CC')
GO
INSERT [dbo].[Products] ([ProductID], [Name]) VALUES (4, N'DD')
GO
Step 2 - Get Information About The Database
First we will check how many data files we have in our source database. Make sure to create a separate sparse file for each data file.
EXEC SP_HELPDB Snapshot_DB
Step 3 - Create New Database Snapshot
Snapshots can only be created by using a T-SQL statement. Here is an example of how to create a database snapshot
CREATE DATABASE Snapshot_DB_28_04_2016 ON
(NAME = Snapshot_DB,
FILENAME = 'D:\DATABASESNAPSHOT\Snapshot_DB_28_04_2016.dbsnapshot')

AS SNAPSHOT OF Snapshot_DB
Step 4 - Delete Data From a Table
Now I will delete all rows from a table, so that we can revert the database snapshot to recover the deleted rows.
USE Snapshot_DB
go
DELETE Products
SELECT * FROM Products
Step 5 - Check To See If Data Still Exists In Snapshot
SELECT * FROM [Snapshot_DB].[dbo].[Products]
Step 6 - Restore Snapshot
USE master
go
RESTORE DATABASE Snapshot_DB
FROM DATABASE_SNAPSHOT='Snapshot_DB_28_04_2016'

SELECT * FROM [Snapshot_DB].[dbo].[Products]
Step 7 - Drop a Database Snapshot 
DROP DATABASE Snapshot_DB_28_04_2016  ;

Benefits of Database snapshot

  1. A database snapshot is convenient, reliable, Read- only, point-in-time copy of the database.
  2. It is very beneficial when doing critical updates to the database as if anything goes wrong database can be restored to that point without any hassle.
  3. If we want to view database suppose at 7 pm, we can create database snapshot and easily query the database, however, without snapshot we have the option to take the database backup and restore using some different name but it requires considerable system resources especially disk space.
  4. Sparse file (snapshot) is small and easy to create however the size of snapshot depends upon the amount of operation and page updated.
  5. Multiple database snapshots are possible for a single database.
  6. There is no dependency for the recovery model, it works with full, bulk-logged and simple also.
  7. Snapshot creation is really quick, it takes a few seconds only for big databases too.

Limitations and cons of Database snapshot

  1. A database snapshot can be created onto the source server only. It cannot be moved to another server
  2. We can’t drop an Original Source Database as long as a referring snapshot exists in that database
  3. It cannot be Backed up and also detach \attach doesn’t work with database snapshots
  4. Snapshots can be created for only user databases, not for the master, model and msdb
  5. If we revert to a snapshot log Chain will get broken, thus we have to take a full or differential backup to bridge the log chain sequence
  6. Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress
  7. If the source database is unavailable or corrupted we cannot use the database snapshot to revert it to the original state
  8. We cannot add new users for database since it is a read-only copy
  9. There is no graphical user interface for creating and reverting back the snapshot, this need to be done from query only
  10. There is some overhead for every DML operation as before operation page needs to be moved out
  11. If the drive in which snapshot exists is out of space which causes any DML to fail the snapshot will be in suspect mode and non-recoverable
  12. The full-text index is not available with a snapshot
  13. It is available with enterprise edition only
  14. Database files that were online during snapshot creation should be online during the snapshot revert also

Restore to a Point in Time

SQL Server allows you to perform the point in time restore by using either T-SQL or SSMS.

To be able to perform a point in time restore you need to restore the full back up which ended prior to the point you would like to restore to , and then restore all the log backups including the one which contains the point you want to get to.
  • Right click on the database you wish to revert back to a point in time
  • Select Tasks/Restore/Database
  • On the restore database dialog select the Timeline option
  • Enter the exact point in time you want the database to be restored to, by either dragging the slider to the desired point, or by selecting it using the date and time picker
  • Click Ok
  • Click Ok again
  • This will start the restore.

Share this article :

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved