May 21, 2007 at 3:37 pm
We have a test server box with much less available space than the dev box where there sits a large db backup (SQL Backup) that we'd like restore from.
I am wondering of a different way - suppose we copy only the strcutures over. Then can we run a script of only certain range of records from the large main tables? Has anybody actually created a such a script where it inserts into the destination database only so many (top) records out of the largest primary table(s) from the source database and still has integrity with other dependent tables? The remaining script would then fill only the data needed by the other tables and do all records of lookups as well.
May 21, 2007 at 4:39 pm
If I understand you correctly, something like this:
INSERT INTO \\testsvr.dbname.dbo.main_table
SELECT TOP 10 *
FROM main_table
INSERT INTO
\\testsvr.dbname.dbo.second_table
SELECT *
FROM second_table
WHERE second_table.id IN (SELECT main_table.id FROM \\testsvr.dbname.dbo.main_table)
Something like that?
One other option is to put all the id's that you are copying from the main_table into a temporary table. Then use the temporary table to determine which rows to move from the other tables.
-SQLBill
May 22, 2007 at 8:35 am
The combination of both options you presented is more along what I'm thinking. I've been thinking a generic metatable SP script to be applied to any database where a copy is desired. Task might be a tad too big yet maybe the thought process can be simplified further.
The script would have to handle potential identity insert issues as well as key constraints. It could pass like 4 parameters: fully qualified source db, fully qualified target db, main table to start with, and id value (or id range like top 10).
The script would find all dependent tables (build temp table with this list?) and generate your 2nd insert statement for each dependent table. The script would also identify tables with no foreign keys and treat them as lookups with full insert statements for them.
May 22, 2007 at 12:09 pm
Have you thought of partitioning the tables? you can place the partitions in certain filegroups and then backup those filegroups.
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply