Saving Bulk Modified or Deleted SQL Database Data for Recovery

Sometimes you need to modify or remove data in bulk in a SQL database. Whatever the reason is, it is always a good practice to store such changes so that if something goes wrong you would be able to restore the data to the original state. Luckily, T-SQL provides the OUTPUT clause which you can use to save such changes. The clause applies to INSERT, UPDATE, DELETE and MERGE statements and allows you to archive the changes, should you later need to recover your original records.

The BNF (Backus–Naur Form) of the OUTPUT clause is:

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
    [ ,...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

The OUTPUT clause comes before the WHERE clause and is pretty straight forward: you specify which columns you want to save and in which table you want to save them. The table you are saving to can be a database table, a table variable or a temporary table.

Let’s look at a hypothetical example – let’s say a website stores account information in a database table called Accounts that has AccountId as the primary key and Email, Password, Name and DateAccessed columns:

+-----------+------------+---------------+------+------------------------+
| AccountId | Email      | Password      | Name | DateAccessed           |
+-----------+------------+---------------+------+------------------------+
|         1 | aaa@me.com | <binary data> |  aaa | 2011-10-02 12:12:54 PM |
+-----------+------------+---------------+------+------------------------+
|         2 | bbb@me.com | <binary data> |  bbb | 2011-01-15 01:33:11 AM |
+-----------+------------+---------------+------+------------------------+
|         3 | ccc@me.com | <binary data> |  ccc | 2010-09-25 05:43:01 PM |
+-----------+------------+---------------+------+------------------------+
|                               ...                                      |
+-----------+------------+---------------+------+------------------------+

You want to delete accounts that were last accessed before 2011-01-01, which is easily done with the following statement:

DELETE FROM [dbo].[Accounts]
	WHERE DateAccessed < '2011-01-01'

However, you want to be able to recover that data should you have a need to. In order to do so, you'll need to create a separate table where these records are stored and use the OUTPUT clause to store them in it. Because we are not modifying records but deleting them, the table should contain all columns that we have in the original table. It may also be a good idea to add another column that will identify when the record was deleted, let's call it DateDeleted:

CREATE TABLE Restore_DeletedAccounts
(
	DateDeleted DATETIME NOT NULL,
	AccountId INT NOT NULL,
	Email NVARCHAR(50) NOT NULL,
	Password IMAGE NOT NULL,
	Name NVARCHAR(100) NOT NULL,
	DateAccessed DATETIME NOT NULL
)

All that's left to do is to run a query that deletes the records and stores them into this newly created table. In this example, it is a single query – in practice, you are likely to encounter multiple queries that you run, whether it's updating or deleting data. So we are going to wrap this into a transaction with a ROLLBACK:

BEGIN TRY
	DELETE FROM [dbo].[Accounts]
		OUTPUT GETDATE(), DELETED.*
			INTO Restore_DeletedAccounts
		WHERE DateAccessed < '2011-01-01'
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	DECLARE @Committed NVARCHAR(15)
    	-- The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.
	IF (XACT_STATE()) = -1
		BEGIN
			ROLLBACK TRANSACTION
			SET @Committed = N'not committed'
		END
	-- The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
	IF (XACT_STATE()) = 1
		BEGIN
			COMMIT TRANSACTION
			SET @Committed = N'committed'
		END
   
	DECLARE @ErrorMessage    NVARCHAR(4000)
	DECLARE @ErrorNumber     INT
	DECLARE @ErrorSeverity   INT
	DECLARE @ErrorState      INT
	DECLARE @ErrorLine       INT
	DECLARE @ErrorProcedure  NVARCHAR(200)

	SELECT	@ErrorNumber = ERROR_NUMBER(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE(),
		@ErrorLine = ERROR_LINE(),
		@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
	SELECT @ErrorMessage = N'Transaction ' + @Committed + N'. Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + ERROR_MESSAGE()
	RAISERROR(
		@ErrorMessage,
		@ErrorSeverity,
		1,              
		@ErrorNumber,
		@ErrorSeverity,
		@ErrorState,
		@ErrorProcedure,
		@ErrorLine)
END CATCH

Note how you can specify * to select all columns from the table we are deleting from. You can also specify individual columns.
Now, if we were to restore our data, all we would need to do is to run this statement:

INSERT INTO Accounts
	SELECT *
		FROM Restore_DeletedAccounts
This entry was posted in MSSQL and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *