Posted by Erland Sommarskog on 07/10/07 21:48

M Bourgon (bourgon@gmail.com) writes:
> I'm trying to automate an auto-export of a table on a daily basis,
> using BCP. I'm using native format for the BCP because the text in
> one of the fields can encompass pretty much any ASCII characters, and
> using the other options (including the null terminator, stuff like
>|||, etc) hasn't worked particularly well.
>
> So, I'm archiving out a table on a daily basis. I want to script out
> the table at the same time; that way, if there are any table changes,
> an import will still work.
>
> How can I do this? I've been digging through google for scripts with
> no luck. Ideally I'd like a table-creation script, along with CREATE
> INDEX statements.
>
> Anybody have a script handy for this? I know it can be done by using
> the system tables, but I'm hoping to avoid reinventing the wheel.
> Thanks in advance.

If you are on SQL 2005, you would use SMO for the scripting and on SQL 2000
it would be DMO. No, I don't have any examples, I have stayed away from
both.

Personally, I would prefer the definition of the table to be under version
control and be content with that.

But why use BCP as a backup tool? Why not simply BACKUP? Or are you
trying to tell us that this is the only table in a big database that
you want to back up?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]