469,926 Members | 1,541 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Enterprise Manager: defaults lost in import/export

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
Jul 20 '05 #1
5 2507
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
Jul 20 '05 #2
"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
Jul 20 '05 #3
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
Jul 20 '05 #4
"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
Jul 20 '05 #5
"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
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Kevin Newman | last post: by
reply views Thread by Crercio O. Silva | last post: by
1 post views Thread by Jurjen de Groot | last post: by
1 post views Thread by Iouri | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.