Copy Whole Database to Another Server

[davewelsh]

I need to programmatically copy all tables from one database to another one (thereby converting file types from SQL to Access in the process). I read about SELECT INTO and INSERT INTO, but to do this I need to know the names of the source tables.

Is there a way of doing this
a) without knowing the table names
or
b) figuring out the names of all the tables in a database?

 

[natrons]

If you are converting between two DBMS's, as it sounds like you are with SQL server and Access, then there are several ways to acheive this. Almost all DBMS's have a way to extract data structure to SQL script files. You can then modify the scripts to the new DBMS dialect and load the DB Structure into the new DBMS. Then extract all of your data to CSV files and load into the new DB.

If you are using MS Access, probably the easiest way to do this is set up an ODBC data source to your old DB, then Choose Import Data from the menu. When the import dialog opens, choose "Files of Type" ODBC. Then connect to your data source and pick what you want to import.

Other than that, we need more specifics of which DBMS(s) you are using and if this will be a one-time conversion or a regular extraction.

Good Luck