How do I archive and restore selected tables using SQL Server?
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
import/export feature in SSMS. You could export out the table to another DB, flat file, or other available formats. If you save your export package, you can rerun it every time you need to export your data. You should have integration services running though.
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.