IN A NUTSHELL:
how to copy a database (by scripting)
i want to copy a database from a development environment exactly (or perhaps as specified in a configuration files) as it is to a production environment. i wish to do this by running a single perl script.
1)
In SQL Server it's possible in the Enterprise Manager GUI to script all tables, which will generate one long sql statement that can be executed and then used to create a fresh new database with all the right dependencies and keys and no content - after that one can import content from the source tables.
That's fine one or two times but rather tedious and error-prone in the long run.
So my question is:
is it possible to achieve the same kind of script possibly via the $dbh->tables(), $dbh->table_info() methods ?
[i rewrote the table info example from the DBI Book sources but i would really like to get my hands on a more robust and elaborate example for SQL server if anyone has one]
2)
assuming i then have a fresh new database and now want to import the content, i have found a scripting solution by "backsticking" using the BCP util - this is almost perfect apart from the fact that it won't work in transaction mode with AutoCommit off [this again isn't a problem on a completely new database but maybe i want to use the procedure on an existing database and only update some of its tables]
So the questions is:
how do you import/export data from the source tables entirely using perl ?
3) lastly, how do people deploy stuff to other environments? Surely i'm not the first one facing this task ;)
many thanks in advance ./allan
