How do I archive and restore selected tables using SQL Server?

  • If you mean real archiving then it depends on the application. If you want to keep the size of the database to a minimum only containing "live" data, you can use partitioning as one method or a separate database. But there can be a lot of work in creating an archive/un-archive solution. Some vendors e.g. Microsoft have some tools for AX Dynamics to handle purging and archiving. Nov 21, 2011 at 21:20

4 Answers 4


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.

related links

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.