Sql Enterprise Edition: 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?

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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog2" wrote:
> steve (UseLinkToEmail@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks, Erland

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

Since I have a ton of tables, I dont 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog2" wrote:
> steve (UseLinkToEmail@.dbForumz.com) writes:
> > I am used to mysql where you just backup table defs and data,
> and
> > easily copy the stuff to a new server.
> > Since I have a ton of tables, I dont 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.
> Thats 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
> dont
> 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 dont 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
dont). 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-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=589880|||"steve" wrote:
> [quote:22bdd234c4="Erland Sommarskog2"]steve
> (UseLinkToEmail@.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
dont just copy tables over using EM.

Thursday, March 22, 2012

Enterprise Manager: defaults lost in import/export

No comments:

Post a Comment

Sql Enterprise Edition

Blog Archive

About Me