Post reply

Script to copy tables and from one database to another

  • February 8, 2011 at 10:32 am

    #100351

    I would like to compose a generic script that would copy tables from one database to another database that do not already exist in the new database.

    I would also like to compare the table structure and identify any changes that have been made between the databses, load the initial data from one database to the other and load incrementals loads there after.

    From what I have read on the forums etc dynamic sql would be the best option besides redgate and apexsql. However I am not that familiar with dynamic sql and would like to follow this route.

    Is it possible for someone to give me a start script or point to some sites where i could get more information on how I could do this in dynamic sql. Ultimately I would like to run the script on anyone of my databases.

    any help would be much appreciated

  • February 23, 2011 at 5:47 pm

    #1290691

    Hi

    You may want to think about this slightly differently. If your goal is to maintain a copy of the database in another database (you seem to want to capture changes to data as well as the DDL) I would suggest you look at the topics "Transaction Log Shipping" and "Database Mirroring".

    Transaction Log Shipping allows you to make those periodic updates so you can get a copy of your database up-to-date from a master of your database. This allows you to determine when you wish to perform those updates. There are limitations (you cannot modify the data in your copy is just one of them).

    Database Mirroring helps you to maintain two versions of your database. The changes are normally completed quite rapidly which may not be what you want.

    Neither of these methods show you the differences but for you to manage the changes in your data they could provide the answer that you really need.

  • February 24, 2011 at 2:08 am

    #1290780

    Thanks for the reply. I eventually came up with a basic script that probably needs a bit of tweaking but the logic is there.

    I will definitely have a look at database mirroring. We have implemented log shipping on some of our other databases but my initial request was to try and find precisely what the differences were between databases.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply