Deleting rows in a SQL database can sometimes be a nerve-racking experience. What if you’re deleting the wrong rows? Or what if the business later tells you they want their data back?

Fortunately SQL Server provides us with an easy way to essentially “back up” any rows affected by a DELETE operation to a table.

This article looks at using the OUTPUT ... INTO clause to save a copy of deleted rows to another table.

Example

Let’s create a table and add data. We’ll also create a logging table that we’ll use to back up any deleted rows to. Finally we’ll go ahead and delete some rows from the original table and then check the contents of the backup table.

Step 1: Create Original Table and Data

Before deleting any data, we first need data. So here we create a small table and populate it with a small amount of data (we don’t need a lot of data to demonstrate this technique):

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (1, 1001, '2023-01-17'),
       (2, 1002, '2023-02-11'),
       (3, 1003, '2023-03-05'),
       (4, 1004, '2023-04-20'),
       (5, 1005, '2023-05-15');

Step 2: Create the Backup Table

This will be the table that contains a back up of the deleted rows:

CREATE TABLE DeletedOrdersBackup (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    DeletedOn DATETIME DEFAULT GETDATE()
);

Now let’s go through and delete some of those records.

Step 3: Delete Some Rows

Now to the main point of this article – delete some rows and save a back up of the deleted rows:

-- Delete the row/s
DELETE FROM Orders
OUTPUT 
    DELETED.OrderID, 
    DELETED.CustomerID, 
    DELETED.OrderDate,
    GETDATE() 
    INTO DeletedOrdersBackup (
        OrderID, 
        CustomerID, 
        OrderDate, 
        DeletedOn
        )
WHERE OrderDate < '2023-03-01';

-- Return the deleted row/s from the backup table
SELECT * FROM DeletedOrdersBackup;

Output:

OrderID  CustomerID  OrderDate                 DeletedOn               
-------  ----------  ------------------------  ------------------------
1        1001        2023-01-17T00:00:00.000Z  2024-09-20T19:58:50.473Z
2        1002        2023-02-11T00:00:00.000Z  2024-09-20T19:58:50.473Z

We can see that two rows were deleted and backed up to our backup table.

Now we can easily restore data back to the original table if required. We can use the DeletedOn column to find relevant data if required (for example, if we find out that someone accidentally deleted the wrong data on a certain date/time).

Note that the backup table can’t:

• Have enabled triggers defined on it.
• Participate on either side of a FOREIGN KEY constraint.
• Have CHECK constraints or enabled rules.

A Cautious Alternative

If you’re extra cautious and decide that you want to backup deleted rows before you do the delete, you can write a SELECT statement that returns all rows to be deleted and use it to insert data into the backup table. Once you’ve confirmed that the correct data is in the backup table, then you can switch the SELECT statement over to a DELETE statement and carry out the delete operation.

Example:

-- Do the insert
INSERT INTO DeletedOrdersBackup (
        OrderID, 
        CustomerID, 
        OrderDate, 
        DeletedOn    
)
SELECT
        OrderID, 
        CustomerID, 
        OrderDate, 
        GETDATE()
FROM Orders
WHERE OrderDate < '2023-04-01';

-- Check the backup table
SELECT * FROM DeletedOrdersBackup;

Output:

OrderID  CustomerID  OrderDate                 DeletedOn               
-------  ----------  ------------------------  ------------------------
1        1001        2023-01-17T00:00:00.000Z  2024-09-20T19:58:50.473Z
2        1002        2023-02-11T00:00:00.000Z  2024-09-20T19:58:50.473Z
3        1003        2023-03-05T00:00:00.000Z  2024-09-20T20:15:48.500Z

Bear in mind that this populates the backup table and provides a DeletedOn date/time before the data is actually deleted. So you might want to design it in such a way that conveys this. Or, if you’re really paranoid, you could have one table for all data identified for deletion, and another table for all data that was actually deleted.