Table level recovery for selected SQL Server tables

By: Tibor Nagy   |   Updated: 2012-11-30   |   Comments (8)   |   Related: > Restore

Problem

I have a production database table that has important data.  If there are any issues with this table I need a quick way to restore this data without having to restore the entire database.  Let's say we have a table called TIPS_TABLE. We need to implement a solution to recover this data as quickly as possible to its previous state if there is an issue with the data in this table. We need to also keep as many changes performed on the other tables intact when we restore this table. What options does SQL Server offer for this problem?

Solution

Microsoft SQL Server has a large variety of database backup and restore functions: you can create full, partial, file, differential and transaction log backups. The selected recovery model significantly influences the backup strategy and therefore the available backup types. A backup and restore solution is one option, but there are other techniques as well.

Option 1: Restore to a Different Database

There is a simple solution which can be appealing, quick and easy. Restore your database to a different database and copy the required table from there to your database.  You could either do a full restore or do a point time recovery restore.  Unfortunately it is not always feasible to restore large databases to a different database because of disk space limitations or time, especially if you are working with a really large database.

Once the database has been restored, you can query across databases, you can use a linked server, use BCP or another option to move the data. Options to import the data are described in this tip. Note that this option will only restore the data, but you should also think about triggers and foreign keys when you update the data.

Option 2: Restore Log Using STOPAT

You might have in mind to restore the recent database backup and roll it to a point in time before the issue happened. You can do this using the STOPAT clause if your database is in the full or bulk-logged recovery model.  The command below shows how to restore the entire database to a specific point in time.

RESTORE DATABASE YourDB
 FROM YourDBBackups
 WITH FILE=3, NORECOVERY;

RESTORE LOG YourDB
 FROM YourDBBackups
 WITH FILE=4, NORECOVERY, STOPAT = 'Oct 22, 2012 02:00 AM';

RESTORE DATABASE YourDB WITH RECOVERY;

The main drawback to this option is that this will overwrite all of your data modifications for all tables which have taken place after the issue occurred. You should consider the amount of data loss acceptable for other tables and implement a backup strategy which suits all needs.

Option 3: Database Snapshot

Another option is to create a database snapshot. When an issue occurs on the table, you can fix its contents using the snapshot as a data source. The snapshot needs to have already been setup before the issue occurs.  This can be useful when our important table is not so frequently updated, especially when it has planned updates with predictable weekly or monthly updates. If there are frequent and parallel updates on the table and different users access it then this might not be the optimal solution.  Note that each time you make data updates on the table a new snapshot will need to be created.

Option 4: Use a View

You can create a second database and move the TIPS_TABLE there. This database would then be backed up separately and when you need to recover the data you can just restore this much smaller database.  To access the data from the original database, the easiest solution would be to create a view which selects all the data from all columns in the TIPS_TABLE. Please note that you will have to rename or drop the table in the original database before creating the view.

USE YourDB;
GO
CREATE VIEW TIPS_TABLE
AS
SELECT *
FROM YourNewDB.YourScheme.TIPS.TABLE;
GO

With this option you can run SELECT, INSERT, UPDATE or DELETE statements against the view just like modifying the actual table. In case of an issue, simply restore the database which contains only your very important table using point in time recovery as described in Option 1. You should keep in mind that the metadata of the view should be updated in case the definition of the columns in TIPS_TABLE changes. You can use the sp_refreshview procedure for this task:

USE YourDB;
GO
EXECUTE sp_refreshview N'TIPS_TABLE';

Option 5: Create a Synonym

Similar to Option 4, move the TIPS_TABLE to a second database. Now implement a synonym for this table in the original database:

USE YourDB;
GO
CREATE SYNONYM TIPS_TABLE
FOR YourNewDB.YourScheme.TIPS.TABLE;
GO

The advantage over the previous solution is that you do not need to worry about a metadata update. One issue with this approach is that you cannot reference a synonym in a DDL statement or if it is located on a linked server.

Option 6: Save the Data Using BCP

You can setup a job to periodically export the data to a flat file using BCP. You can create a scheduled job using SQL Server Agent or create a trigger. The disadvantages are similar to Option 1. Then when there is an issue you would need to import the data back into the table from the flat file.

Comparison of Each Method

I hope that this overview helped you understand the available options for a table level backup and restore. Every solution has its advantages and its disadvantages. You should decide what factors are more important in your shop and choose the options which best suits your needs.

Next Steps

• Check out these Backup Tips and Restore Tips

About the author

Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips