-1

Hi i' d like to know is there any way to create a rollback copy of a table in SQL server just in case i do wrong insert or update statement i'd like to recover my data as it was before that insert or update statements.

deep
  • 41
  • 1
  • 12
  • Why not put your script inside of a transaction with a rollback until you get the script correct? There is really no need to create a copy of the entire table for this. – Sean Lange Nov 27 '17 at 15:24
  • i have putted my script inside a transaction and it is running fine, but still for safety reason i need a copy of my table. – deep Nov 27 '17 at 15:27
  • 1
    The rollback is your safety net. If you are deadset on creating a copy of your table then look at SELECT INTO. – Sean Lange Nov 27 '17 at 15:28
  • actually i need it because it's happened that the script was running fine everything was correct and i was using transaction as well, but once i executed the query and i got to know that i have updated wrong record so thats why i'd like to have a rollback copy of a table. – deep Nov 27 '17 at 15:42
  • So you create a table and then use an insert statement. Or you could use select into if you want to create the table. – Sean Lange Nov 27 '17 at 15:51
  • Possible duplicate of [SQL Server - Create a copy of a database table and place it in the same database?](https://stackoverflow.com/questions/15428168/sql-server-create-a-copy-of-a-database-table-and-place-it-in-the-same-database) – Tab Alleman Nov 27 '17 at 16:02
  • If your edition supports it, another option is to take a [snapshot](https://learn.microsoft.com/sql/relational-databases/databases/database-snapshots-sql-server) and revert to that. This reverts an entire database rather than a table, which may or may not be what you want. – Jeroen Mostert Nov 27 '17 at 16:12
  • one way would be is by using trigger. create another table and log your changes in that table using triggers. – Kashif Qureshi Nov 27 '17 at 16:58

2 Answers2

0
SELECT * 
  INTO myBackupTableName
  FROM Yourtable

Creates a backup of the table.

Zorkolot
  • 1,899
  • 1
  • 11
  • 8
0

Assuming that we are discussing a production environment and workload: The more I think about this question/requirement the more strongly I believe rollback is the best answer.

Summarizing suggestions already made:

Select into to create a backup table will create a copy of the table but if you revert to it you will potentially be losing data from other users or batches.

Using the into clause from a select or update statement will get you the changed rows but not the original value.

Another answer would be to create an audit table and use a trigger to populate it. Your audit table would need to include enough details regarding the batch to identify it for rollback. This could end up being quite a rabbit hole. Once you have the trigger on the base table and the audit table you will then need to create the code to use this audit table to revert your batch. The trigger could become a performance issue and if the database has enough changes from enough other users then you still would not be able to revert your batch without possibly losing other users' work.

You can wrap your update AND your validation code inside the same proc and if the validation fails only your changes are rolled back.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql

John
  • 133
  • 9