CODEWITHSUNDEEP
sql-server-express
MyDaftQuestions
MyDaftQuestions

Reputation: 4691

How to export SQL datas a

I'm trying to export data from 1 SQL Server EXPRESS to another SQL Server EXPRESS (2 servers).

A quick research shows there is an export / import wizard which nearly does what I want.

The problem I have is, although I enable identity insert, when I export or import, the primary keys are gone, as are all of my constraints/indexes/unique keys etc. This means I have to manually add the keys again...

How can I, whilst only using SQL Express or scripts, copy the data and include everything. If I copied the various database files over, would this do it (and if so, what are the files)?

Upvotes: 0

Views: 152

Answers (2)

David Atkinson
David Atkinson

Reputation: 5899

Another option is to use a data comparison tool such as Redgate SQL Data Compare as this disables and re-enables the constraints as required to copy over the data.

There's a 14-day fully functional trial. I work for Redgate, so let me know if you have any further questions.

Upvotes: 2

Greg
Greg

Reputation: 4045

As you said, the easiest option would be to take a database backup, and restore it to the destination. Another option would be to use bcp.exe, which is similar to what import/export is doing, but that requires you to script out all the dependency objects (indexes, constraints, etc.), which can be easily done via SSMS. Regarding the identity column, when you bcp in, you set the parameter to "keep identity values", so it inserts the same values that were there before.

Upvotes: 1

Related Questions