I have a large database (200GB+).
I need to create a QA version of it but I have limited storage space.
99% of the data is contained in 2 tables but the content of these tables is historic, not needed in the QA.
I have an initialization script that can create the QA database with all objects minus the data.
I know I can export/script out data from the production and bring it into the QA table by table, in the order of the dependencies, leaving out the data.
Can you suggest a faster way? There are quite a few tables in the database...
TIA
Look into redgate for migrating just the skeleton only, the data part should be easy via Ssis, or some other scripting language
If you have the space, even if only briefly, to do the restore, then maybe this:
Move the two large tables into a separate filegroup;
Restore the full db to QA, then immediately truncate and shrink the two large tables. If necessary, you could save 1000/5000/some small number of the rows into a separate table and re-insert them after the truncate, so the table's won't be completely empty.
Or just buy a cheap drive and restore the big-2-table-filegroup to very slow storage since you'll almost never read it anyway.