How do you copy a MS SQL 2000 database programmatically using C#?

Question

I need to copy several tables from one DB to another in SQL Server 2000, using C# (VS 2005). The call needs to be parameterized - I need to be able to pass in the name of the database to which I am going to be copying these tables.

I could use DTS with parameters, but I can't find any sample code that does this from C#.

Alternatively, I could just use

drop table TableName
select * into TableName from SourceDB..TableName

and then reconstruct the indexes etc - but that is really kludgy.

Any other ideas?

Thanks!

When you say you can't find c# sample code for DTS, do you mean sample code to execute a DTS package, or sample code to copy tables within a DTS package? — kristian
– kristian, Commented Oct 6, 2008 at 15:57

Pittsburgh DBA · Accepted Answer · 2008-10-06 16:41:00Z

3

For SQL Server 7.0 and 2000, we have SQLDMO for this. For SQL Server 2005 there is SMO. This allows you do to pretty much everything related to administering the database, scripting objects, enumerating databases, and much more. This is better, IMO, than trying a "roll your own" approach.

SQL 2000: Developing SQL-DMO Applications

Transfer Object

SQL 2005: Here is the SMO main page: Microsoft SQL Server Management Objects (SMO)

Here is the Transfer functionality: Transferring Data

How to: Transfer Schema and Data from One Database to Another in Visual Basic .NET

edited Oct 6, 2008 at 16:41

answered Oct 6, 2008 at 16:32

Pittsburgh DBA

6,8042 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kev · Accepted Answer · 2008-10-06 14:34:31Z

1

If the destination table is being dropped every time then why not do SELECT INTO? Doesn't seem like a kludge at all.

If it works just fine and ticks all the requirements boxes why create a days worth of work growing code to do exactly the same thing?

Let SQL do all the heavy lifting for you.

answered Oct 6, 2008 at 14:34

Kev

120k53 gold badges308 silver badges396 bronze badges

1 Comment

Shaul Behr Over a year ago

It can get tricky with FK dependencies and stuff like that. I'm not saying it wouldn't work, it just strikes me as being very inelegant.

Saif Khan · Accepted Answer · 2008-10-06 14:43:50Z

1

You could put the scripts (copy db) found here

http://www.codeproject.com/KB/database/CreateDatabaseScript.aspx

Into an application. Just replace the destination. To actually move the entite database, FOLLOW

http://support.microsoft.com/kb/314546

But remember, the database has to be taken offline first.

Thanks

answered Oct 6, 2008 at 14:43

Saif Khan

18.9k30 gold badges106 silver badges147 bronze badges

2 Comments

Shaul Behr Over a year ago

Would be nice, but the CodeProject script is for SQL 2005; I only have SQL 2000.

Saif Khan Over a year ago

Look at the Books Online for SQL Server 2000, type "Copy Database" and you'll get information there.

Collectives™ on Stack Overflow

How do you copy a MS SQL 2000 database programmatically using C#?

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related