I have 2 separate SQL Server instances, for the purpose of the question, we will call them
I have an ever changing database on
MyDatabase, this database is populated once a day from a piece of software. When this is dumped, I need to create a copy of the database on
Target. It must be an exact replica.
My first method was using a SSIS package, to export and import the data, however will this method work if the database schema is changing?
For example, my package copies:
Target.Table1 (ColA, ColB, ColC) Target.Table2 (ColA, ColB, ColC)
After my package has been set up, the schema becomes:
Target.Table1 (ColA, ColB, ColC) Target.Table2 (ColA, ColB, ColD) (ColC has been dropped) Target.Table3 (ColA, ColB)
How can I insure the changes in Table2 are picked up, and Table3 is exported and imported?
Sorry I can't give much more of an example, however hopefully this is enough detail for the question.