{[['
']]}
']]}
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 thenTo 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 table for testingCREATE DATABASE [Snapshot_DB]CONTAINMENT = NONEON 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
Step 2 - Get Information About The DatabaseUSE [Snapshot_DB]GOCREATE TABLE [dbo].[Products]([ProductID] [int] NULL,[Name] [nchar](10) NULL) ON [PRIMARY]GOINSERT [dbo].[Products] ([ProductID], [Name]) VALUES (1, N'AA')GOINSERT [dbo].[Products] ([ProductID], [Name]) VALUES (2, N'BB')GOINSERT [dbo].[Products] ([ProductID], [Name]) VALUES (3, N'CC')GOINSERT [dbo].[Products] ([ProductID], [Name]) VALUES (4, N'DD')GO
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
Snapshots can only be created by using a T-SQL statement. Here is an example of how to create a database snapshot
Now I will delete all rows from a table, so that we can revert the database snapshot to recover the deleted rows.
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.
Step 4 - Delete Data From a TableCREATE DATABASE Snapshot_DB_28_04_2016 ON(NAME = Snapshot_DB,FILENAME = 'D:\DATABASESNAPSHOT\Snapshot_DB_28_04_2016.dbsnapshot')
AS SNAPSHOT OF Snapshot_DB
Now I will delete all rows from a table, so that we can revert the database snapshot to recover the deleted rows.
Step 5 - Check To See If Data Still Exists In SnapshotUSE Snapshot_DBgoDELETE ProductsSELECT * FROM Products
SELECT * FROM [Snapshot_DB].[dbo].[Products]Step 6 - Restore Snapshot
Step 7 - Drop a Database SnapshotUSE mastergoRESTORE DATABASE Snapshot_DBFROM DATABASE_SNAPSHOT='Snapshot_DB_28_04_2016'SELECT * FROM [Snapshot_DB].[dbo].[Products]
DROP DATABASE Snapshot_DB_28_04_2016 ;
Benefits of Database snapshot
- A database snapshot is convenient, reliable, Read- only, point-in-time copy of the database.
- 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.
- 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.
- Sparse file (snapshot) is small and easy to create however the size of snapshot depends upon the amount of operation and page updated.
- Multiple database snapshots are possible for a single database.
- There is no dependency for the recovery model, it works with full, bulk-logged and simple also.
- Snapshot creation is really quick, it takes a few seconds only for big databases too.
Limitations and cons of Database snapshot
- A database snapshot can be created onto the source server only. It cannot be moved to another server
- We can’t drop an Original Source Database as long as a referring snapshot exists in that database
- It cannot be Backed up and also detach \attach doesn’t work with database snapshots
- Snapshots can be created for only user databases, not for the master, model and msdb
- 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
- Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress
- If the source database is unavailable or corrupted we cannot use the database snapshot to revert it to the original state
- We cannot add new users for database since it is a read-only copy
- There is no graphical user interface for creating and reverting back the snapshot, this need to be done from query only
- There is some overhead for every DML operation as before operation page needs to be moved out
- 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
- The full-text index is not available with a snapshot
- It is available with enterprise edition only
- 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.
Post a Comment