SQL Server Database Modeling and Design
Use caution when backing up or restoring a single table
By Serdar Yegulalp, Contributor
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
10 Oct 2005
Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.