Backup a portion of the database?

Question

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?

Answer 1

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.

Answer 2

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 dba.se 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.

Answer 3

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.