Recover Data Using Database Snapshots

Recover Data Using Database Snapshots
Now in order to get the deleted records back in Address table you can choose any one option among the two mentioned. One will be to restore the SourceDB database from the database snapshot as you don’t have full backup available for the database. The second option will be to use INSERT INTO … SELECT option to repopulate the Address table from the database snapshot. Now let us see both the options in detail.

Execute the below mentioned TSQL code to restore SourceDB database from the SourceDB_Snapshot database snapshot.

USE master
GO

RESTORE DATABASE SourceDB
FROM DATABASE_SNAPSHOT =’SourceDB_Snapshot’
GO

Execute the below mentioned TSQL code to populate Address table records from Database Snapshot using

INSERT INTO … SELECT option.
INSERT INTO SourceDB.dbo.Address
SELECT * FROM SourceDB_Snapshot.dbo.Address
GO

Dropping Database Snapshot
Database Administrators can remove a database snapshot either by using the DROP DATABASE command or by right clicking the SourceDB_Snapshot and selecting the delete option in SQL Server Management Studio as shown in the below snippet.

Execute the below TSQL code to drop the SourceDB_Snapshot database snapshot.

USE master
GO
DROP DATABASE SourceDB_Snapshot
GO

Advantages of Database Snapshots

1. They are very easy to create and take very less time when compared to taking the Full database backups.
2. They require very less disk space when compared to Full database backups, However database backups are always a better option.
3. It is a static, read-only copy of user database at a given point in time, all the uncommented transactions are rolled back to make the snapshot transactionally consistent at the given time.
4. They basically contain the copy of data pages prior to the data modification in the source database.
5. Reports can be run against database snapshots, the users will not experience blocking because of update / insert operations when they are connected to database snapshots which they normally experience when connected to source database.
6. Database Administrators can create database snapshot for historical data
7. Database Administrators can easily recover the corrupted or deleted data from the database snapshot while repairing the primary database
8. Test team can quickly revert to old state of database by quickly restoring the database from database snapshots.
9. You can create database snapshots for all the user databases irrespective of the size of the database.

Disadvantages of Database Snapshots

1. You won’t be able to detach or attach the snapshot database.
2. You won’t be able to backup or restore snapshot database.
3. Both source database and database snapshot should exist on the same server.
4. Full Text indexes are not supported on database snapshots.
5. It is not possible to add or drop any objects in the snapshot database as it is a read-only database.
6. Database snapshot feature is only available in Enterprise Edition of SQL Server 2005 & SQL Server 2008.
7. If your source database is not available by any chance then database snapshots are of no use. Queries which are run against snapshots are indirectly getting the data from the actual source database.
8. If there are lot of changes suppose to happen in source database for which database snapshot is created then the size of the snapshot will grow quickly and in that case the best option will be to take full database backups.
9. This can also affect the performance as the data pages are copied to database snapshots whenever changes are done to the existing data in the source database.
10. You cannot create a database snapshot against any of the system databases.

Conclusion
Database Snapshots are an excellent feature which was introduced in SQL Server 2005. Database Administrators can take database snapshot prior to making any structural changes to a user database. However, you need to understand that database snapshots are not a replacement to database backups, at any point in time database backups are always a better option. If there are any issues encountered by a DBA while making structural changes then by restoring a database from a database snapshot you can quickly revert to aprevious version of the database. I hope in future releases of SQL Server that this feature will be made available in all editions of SQL Server. 

]]>