How do I archive and restore selected tables using SQL Server?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You have to use a 3rd party tool such as Red Gate SQL backup pro to restore. Or restore as a different DB and copy across.
Selective restore makes sense, occasionally, but selective backup usually wouldn't. You-d have to roll your own selective backup with SMO for example
You could use the
The same can be done for importing data back into the table. You can choose to append or overwrite the data. A bit of tweaking will have to be done to get the file formats the way you want it.
The only way to do this without 3rd party tools is by selectively backing up and restoring different filegroups, which requires some deliberate setup within the database.
In our environment, we use the "Object Level Recovery" feature of Quest Litespeed.
The workaround, as @gbn mentioned, is to restore a copy of the database with a different name, then copy the table/rows out that you need.
If it is a one time activity: Use import/export feature as already suggested. That feature uses SSIS on the background therefore you can tweak it.
If these backup and restore is continuous activity not a one time activity. Consider a database: some tables are not really important as others. For example invoice tables vs log tables (not transactional log files but monitoring tables). You would like to backup invoice tables more regularly than log tables. Log tables consume a lot of space but they are not as important as invoice data.
Consider using file group backups instead of full backups. Put all really important tables in one file group and other tables in another file group.