Enterprise Manager: defaults lost in import/export

[steve]

Hi,
When I copy tables in a database from one server to another using
enterprise manager, everything copies ok, except for field defaults.

Has anyone seen this, and what is the solution?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Disc...ict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=586973

[Erland Sommarskog]

steve (Us************@dbForumz.com) writes:

When I copy tables in a database from one server to another using
enterprise manager, everything copies ok, except for field defaults.

Has anyone seen this, and what is the solution?

No, I have not seen it. Then again, I never copy tables with Enterprise
Manager.

The way that metadata should be installed in my opinion are from scripts
kept under source control. Relying on tools that you don't know what they
do under the cover is not a reliable process.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[steve]

"Erland Sommarskog2" wrote:

steve (Us************@dbForumz.com) writes:

When I copy tables in a database from one server to another

using

enterprise manager, everything copies ok, except for field

defaults.

Has anyone seen this, and what is the solution?

No, I have not seen it. Then again, I never copy tables with
Enterprise
Manager.

The way that metadata should be installed in my opinion are
from scripts
kept under source control. Relying on tools that you don't
know what they
do under the cover is not a reliable process.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks, Erland

I am used to mysql where you just backup table def’s and data, and
easily copy the stuff to a new server.

Since I have a ton of tables, I don’t want to set up anything by hand.
Is there an easy and bulletproof way to transfer data and contents
(and views and stored procedures) from one sever to another?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Disc...ict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=588260

[Erland Sommarskog]

steve (Us************@dbForumz.com) writes:

I am used to mysql where you just backup table def's and data, and
easily copy the stuff to a new server.

Since I have a ton of tables, I don't want to set up anything by hand.
Is there an easy and bulletproof way to transfer data and contents
(and views and stored procedures) from one sever to another?

If you want to transfer the entire database, just use BACKUP/RESTORE.
That's far more robust than scripting the lot. An alternative is
to use sp_detach_db/sp_attach_db.

The only time this is not possible is when you for some reason need to
change the collation. (Or the database is corrupt.) In this, if you don't
have scripts for your database under version control, you can script the
database from Enterprise Manager to det the definitions, and use BCP
to bulk data in and out. But relying on scripting is deceivable as you
have noticed. (I don't know what the Export/Import wizard does, but it
probably packages scripting and BCP:ing. I have never used it.)

If you want to move the metadata and data for some other reason, for
instance shipping from a development environment to a production
environment, then a version-control system is essential.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[steve]

"Erland Sommarskog2" wrote:

steve (Us************@dbForumz.com) writes:

I am used to mysql where you just backup table def’s and data, and

easily copy the stuff to a new server.

Since I have a ton of tables, I don’t want to set up anything

by hand.

Is there an easy and bulletproof way to transfer data and contents (and views and stored procedures) from one sever to another?

If you want to transfer the entire database, just use

BACKUP/RESTORE. That’s far more robust than scripting the lot. An alternative is
to use sp_detach_db/sp_attach_db.

The only time this is not possible is when you for some reason need to change the collation. (Or the database is corrupt.) In this, if you
don’t
have scripts for your database under version control, you can script the
database from Enterprise Manager to det the definitions, and use BCP to bulk data in and out. But relying on scripting is deceivable as you have noticed. (I don’t know what the Export/Import wizard does,
but it
probably packages scripting and BCP:ing. I have never used it.)

If you want to move the metadata and data for some other reason, for instance shipping from a development environment to a production
environment, then a version-control system is essential.

Hi Erland,

Backup/Restore is really good BUT cannot be used with a remotely
hosted server (if one does not have access to the file system, which I
don’t). I am still at a loss for an easy solution.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Disc...ict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=589880

[steve]

"steve" wrote:

[quote:22bdd234c4="Erland Sommarskog2"]steve
(Us************@dbForumz.com) writes:

I am used to mysql where you just backup table def's and

data, and

easily copy the stuff to a new server.

Since I have a ton of tables, I don't want to set up

anything by hand.

Is there an easy and bulletproof way to transfer data and

contents

(and views and stored procedures) from one sever to another?

If you want to transfer the entire database, just use
BACKUP/RESTORE.
That's far more robust than scripting the lot. An alternative
is
to use sp_detach_db/sp_attach_db.

The only time this is not possible is when you for some reason
need to
change the collation. (Or the database is corrupt.) In this,
if you don't
have scripts for your database under version control, you can
script the
database from Enterprise Manager to det the definitions, and
use BCP
to bulk data in and out. But relying on scripting is
deceivable as you
have noticed. (I don't know what the Export/Import wizard
does, but it
probably packages scripting and BCP:ing. I have never used
it.)

If you want to move the metadata and data for some other
reason, for
instance shipping from a development environment to a
production
environment, then a version-control system is essential.
[/quote:22bdd234c4]

Hi Erland,

Backup/Restore is really good BUT cannot be used with a
remotely hosted server (if one does not have access to the
file system, which I don't). I am still at a loss for an easy
solution.

I think I have figured out a strategy with Enter. Manager (EM) that
works.

First, I have EM create me a file which includes all the sql create
statements. Then I execute that on the server where I like to migrate
the db to. I can do that using Query Analyzer.

Now I have all the "metadata" properly set up. Next step is to copy
the records over using EM, which is easy.

So the key is to use the above process to create the tables FIRST, and
don’t just copy tables over using EM.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Disc...ict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=590002