Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
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. – Anders Gregersen Nov 21 '11 at 21:20

4 Answers

up vote 4 down vote

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

share|improve this answer
up vote 3 down vote

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.

share|improve this answer
up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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