none
how to roll back to the history of Database in sql server 2008 RRS feed

  • Question

  • Hi all,

    I am designing a sql server oriented Database . using C# code . the data base will be edited by multy user environment.

    in case any user want to roll back to the condition of a particular time what will be the best method.

    thanks in advance 

    Iqbal


    itismeiqbal

    Thursday, May 31, 2012 2:24 PM

Answers

  • You need to capture and preserve the state of the data inside database. For that you will need to create a DB schema that will accommodate or use log scanner mechanism i.e CDC.

    You need basically an Auditing mechanism which will store the old data (kind of history) on your database. Various options are mentioned here

    http://en.wikipedia.org/wiki/Change_data_capture

    If you want this feature for the while database, I will recommend to use CDC although it is designed for Data Warehousing but still you can introduce two columns on each table like Username and Modified Date and use Rollback Script mentioned here

    http://www.mattwilhoite.com/journal-matt/2010/1/19/using-cdc-for-rollbacks-in-sql-2008.html

    Please note that CDC may reduce performance and need high disk space.

    For limited functionalities you can have a separate table to maintain the History and from there you can write an update query to Rollback.


    Lingaraj Mishra


    Friday, June 1, 2012 9:16 AM