Use caution when backing up or restoring a single table

It is possible to back up and restore a single SQL Server table -- but certain precautions must be taken or data integrity may be compromised. Learn the best way to handle a single-table restore in this tip by contributor Serdar Yegulalp.

SQL Server database backups are monolithic. When you back up the database, you're actually backing up all of its

tables, stored procedures, users, functions, etc., at once. This design helps SQL Server defend data and referential integrity. Yet you may need to restore data from a single table, possibly in the event that an accidental delete operation occurred or if you need to view data "out of context" for study or use elsewhere. While it is possible to back up and restore a single table, there are some cautions to be aware of.

The best way to handle a single-table restore is to isolate changes to the database as much as possible, so if something goes wrong at any step in the restore process (i.e. if integrity breaks), you still have something to fall back on. It's best to restore such data from a backup of the database itself. The backup should be the most recent available so only minimal changes will appear in the rest of the data. You can restore such a backup under a different name, and use a DTS statement to copy the needed data from the backup database to the live database. Be sure to make a totally separate backup of the live database first before doing this -- just to be extra-careful.

The problem gets thornier if the table in question has references to other tables. For instance, if that table had triggers or relationships that caused other tables to change, then those tables will also need to be restored. You could also "roll forward" any needed changes made in your live database to the restored backup, again via DTS, then drop the live database and make the restored backup your live database. If you have backups of everything involved (you'd better), you can revert to them if necessary. This way changes are copied out of your live database and not into it, so users can continue working mostly uninterrupted until you get everything squared away.


More information from SearchSQLServer.com

  • Step-by-Step Guide: Ensuring data integrity in SQL Server
  • Tip: Building a scalable SQL Server
  • Tip: Preserving Unicode data integrity


  • This was first published in October 2005

    Dig deeper on SQL Server Database Modeling and Design

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close