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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database that is over 50Gb. I'd like to take a backup of this database, but I don't want a full backup. I need to extract some of the data for a development test db. Is this possible?

share|improve this question

migrated from Jan 11 '12 at 12:29

This question came from our site for system and network administrators.

You might find this to be most helpful. – Bart Silverstrim Jan 11 '12 at 10:41
up vote 1 down vote accepted

Using the SQL Server management console, you can choose the database and Export option to export the table definition and data. Then, you can import this data to your test data base.

Hope you have already created the test database manually. While importing data, it will automatically create the tables, if they are not already exists.

share|improve this answer

If I understand the OP correctly they want to extract a subset of the data from all tables, not all of the data from some tables.

This posting on discusses a similar question. In short, backup/restore isn't the way to do this, and I'm not aware of any off the shelf tool that can do it without quite a lot of manual intervention.

The OP will have to extract the correct child records for every relationship and start from one or more roots, going down to get all the related entities. Not worth the trouble for reference data - you're better off just copying the whole table, and the number of tables with transactional dat a will probably only be a fraction of the total number of tables in the schema.

Just copy the whole data set except for the small handful of really large tables. Write queries for those that get out the subset of data you want. Extract them as a CSV and bulk insert them, or embed the queries in SSIS packages.

share|improve this answer

You can only back up the entire database if you intend to restore it elsewhere.

If the database consists of multiple datafiles you can back these up separately but all the files are needed to restore.

You may be better off setting up snapshot replication for the tables you are interested in.

share|improve this answer

Your Answer


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.