"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/techinfo/productdoc/2000/books.asp

> 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?

"Erland Sommarskog2" wrote:
> 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.
>

> 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?

"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.