Restore Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore Database

Author

Topic

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2004-10-12 : 10:42:07

Hello All,
I have a situation here. I have a backup of database 200GB. I have to restore the database, catch is that the database has about 70 tables and i need to restore only 10% of data.

For Example: Say database is Customers. I need to restore the data for first 100 customers. I am trying to explain clearly. Hope you guys under stood.

Thanks,
Rick

jgsteeler
Starting Member

12 Posts

Posted - 2004-10-12 : 10:54:54

I think I would restore two copys of the database. Tuncate the tables in one copy to empty them , then I would use DTS to pull the data that you want from the 200 GB copy to the clean copy.

Always look on the bright side of life, ta dum, ta dum.

Monthy Python, The Life of Brian

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2004-10-12 : 11:20:30

Wondering how would i do DTS to pull only 100 records of customers information from the backup.... Or is there any way i can take a backup of database restricting data for that number of customers

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:23:59

Are you trying to make a subset of data for testing, or something similar? Something like this perhaps?

SELECT TOP 100 *
INTO NewDataBase.dbo.CUSTOMERS
FROM OldDataBase.dbo.CUSTOMERS

SELECT *
INTO NewDataBase.dbo.ORDERS
FROM OldDataBase.dbo.ORDERS
WHERE ORDERS.CustomerID in (SELECT CustomerID FROM NewDataBase.dbo.CUSTOMERS)

Kristen

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2004-10-13 : 11:50:22

Yes Kristen You are correct....But its not just one table....It has 48 tables. And custid is the key column and every table has references. I have to pull out out data for specific custids from all 48 tables.

ricky_newbee
Posting Yak Master

126 Posts

Posted - 2004-10-13 : 13:03:36

Any Ideas?

Kristen
Test

22859 Posts

Posted - 2004-10-13 : 13:04:39

Well I've done two of them for you ... what are you waiting for? !!

I can't think of a smart way. You could possible use the FOREIGN KEY info in the System tables to build you some SELECT * INTO type SQL, which you coudl then just run, but it's probably not worth it for 48 tables - unless you are likely to need a simialr tool again in the future.

Those www.red-gate.com people don't have anything that will do the deed, do they?

If you go down the route of leveraging the FK info into some SDQL I would probably change the example I wrote above to:

INSERT NewDataBase.dbo.ORDERS
SELECT ORDERS.*
FROM OldDataBase.dbo.ORDERS
JOIN NewDataBase.dbo.CUSTOMERS
ON CUSTOMERS.CustomerID = ORDERS.CustomerID

because its probably a bit easier to generate than all that WHERE ... IN ... stuff, and once you've got your first 100 customers all the associated data "just happens"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources