Exporting databases 1

[Flybridge]

I have a database in SQL 2000 that for some reason has duplicated files with different database owners.

I need to copy the tables, but just for 1 dbo. Looking at the Export routine I will have to scroll down and tick just the tables I want. As there are over 3000 tables in this application I would like to be able to run a routine that allows me to select a data base owner and just export those tables.

Does anyone have, or know how I could get, such a query/routine?


'The world isn't round - it's bent!' Spike Milligan

 

[TheBugSlayer]

You probably will have to export the tables of your choice to text files and then import them in the new database. You will then refair to the objects by their fully qualified name as in [Database].[Owner].

. The Owner part can be a variable in which case you will have to construct your statement dynamically.

 

[mrdenny]

When using the Import/Export wizzard you'll need to manually select the tables you want.

You can write a batch files to BCP out the objects you want.

Do something like:

select 'BCP "Database.' + TABLE_SCHEMA + '.' + TABLE_NAME + '" OUT "c:\Database.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt" -T -w -S SERVERNAME -b 1000'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'

Take the output from this query and put it in a batch file, then run the batch files. It will export all the data from the tables owned by DBO into text files.

You can then change the direction from OUT to IN and import the data into another database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Flybridge]

Thanks.

I knew someone would have the knowledge. Not being a fully fledged techie I can't quite get my head around these things.


'The world isn't round - it's bent!' Spike Milligan

 

[mrdenny]

no problem

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com