restore only certain tables - 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 only certain tables

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-10 : 08:34:03

I have the backups and our tables are owned by
TC_0002
TC_0004
TC_0008

I only need to restore TC_0002 from the backup....

I cannot think of a way within restoring the entire bak somewhere and then writing scripts to take
RESTOREDDB
delete from ORIGNALDB.TC_0002.TABLENAME
INSERT INTO ORIGNALDB.TC_0002.TABLENAME
select from RESTOREDB.TC_0002.TABLENAME

But then i run into which one first because of relationships etc....

Any suggestions

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-10 : 09:51:22

It sounds like you already know how to do it.

You can use this script to figure out the order to load the tables.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

CODO ERGO SUM

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-10 : 12:56:38

Just concerned now with parent and child.
Is there a way to remove the FK and then put back after with a script.
so there are no parent and child relationship just purely single tables.
I think there are triggers on there too....

Thanks

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-11-10 : 13:05:58

just looked at the script.

i ran it against northwind

Load tables for database Northwind

(15 row(s) affected)

Load PK/FK references

(13 row(s) affected)

Make copy of PK/FK references

(13 row(s) affected)

Load un-referenced tables as level 0

(2 row(s) affected)

Remove self references

(1 row(s) affected)

Delete lower level references

(0 row(s) affected)

Load level 1 tables

(7 row(s) affected)

Delete lower level references

(9 row(s) affected)

Load level 2 tables

(4 row(s) affected)

Delete lower level references

(3 row(s) affected)

Load level 3 tables

(2 row(s) affected)

Done loading table levels

Count of Tables by level

REF_LEVEL TABLE_COUNT
----------- -----------
0 2
1 7
2 4
3 2

(4 row(s) affected)

Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference

REF_LEVEL TABLE_NAME
----------- ----------------------------------------
0 dbo.ACTUAL_HOURS
0 dbo.Pivot
1 dbo.Categories
1 dbo.CustomerDemographics
1 dbo.Customers
1 dbo.Employees
1 dbo.Region
1 dbo.Shippers
1 dbo.Suppliers
2 dbo.CustomerCustomerDemo
2 dbo.Orders
2 dbo.Products
2 dbo.Territories
3 dbo.EmployeeTerritories
3 dbo.Order Details

(15 row(s) affected)

Tables and Referencing Tables

REF_LEVEL TABLE_NAME REFERENCING_TABLE
----------- ---------------------------------------- ----------------------------------------
0 dbo.ACTUAL_HOURS NULL
1 dbo.Categories dbo.Products
2 dbo.CustomerCustomerDemo NULL
1 dbo.CustomerDemographics dbo.CustomerCustomerDemo
1 dbo.Customers dbo.CustomerCustomerDemo
1 dbo.Customers dbo.Orders
1 dbo.Employees dbo.Employees
1 dbo.Employees dbo.EmployeeTerritories
1 dbo.Employees dbo.Orders
3 dbo.EmployeeTerritories NULL
3 dbo.Order Details NULL
2 dbo.Orders dbo.Order Details
0 dbo.Pivot NULL
2 dbo.Products dbo.Order Details
1 dbo.Region dbo.Territories
1 dbo.Shippers dbo.Orders
1 dbo.Suppliers dbo.Products
2 dbo.Territories dbo.EmployeeTerritories

(18 row(s) affected)

Tables and Tables Referenced

REF_LEVEL TABLE_NAME TABLE_REFERENCED
----------- ---------------------------------------- ----------------------------------------
0 dbo.ACTUAL_HOURS NULL
1 dbo.Categories NULL
2 dbo.CustomerCustomerDemo dbo.CustomerDemographics
2 dbo.CustomerCustomerDemo dbo.Customers
1 dbo.CustomerDemographics NULL
1 dbo.Customers NULL
1 dbo.Employees dbo.Employees
3 dbo.EmployeeTerritories dbo.Employees
3 dbo.EmployeeTerritories dbo.Territories
3 dbo.Order Details dbo.Orders
3 dbo.Order Details dbo.Products
2 dbo.Orders dbo.Customers
2 dbo.Orders dbo.Employees
2 dbo.Orders dbo.Shippers
0 dbo.Pivot NULL
2 dbo.Products dbo.Categories
2 dbo.Products dbo.Suppliers
1 dbo.Region NULL
1 dbo.Shippers NULL
1 dbo.Suppliers NULL
2 dbo.Territories dbo.Region

(21 row(s) affected)

Can you help me explain which to do first i got confused with the output of script.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources