How to update database data on schedule once a day from another database sql server 2008 r2 ssis, script or restore

Question

Looking for correct approach to update database daily on a schedule from another database and including all changes made but keeping some reporting data.  Both sql server 2008 r2.

The difference is the source database does not contain data considered historical as it was moved to the target database already to keep the bulk of data available for reporting (which is also why it only needs to be updated once a day as it is just intended for historical reporting)  The source database must not include this historical data that was moved.

I typically would have scheduled a job to do a restore of a bak if I wanted a copy but this isn't going to work as the bak doesn't include the report data as it only resides in the reporting target database and I'd overwrite it restoring the bak (unless there is a restore option I don't know about)

I tried setting up an SSIS using wizard and it made the control flow and the data flow for all tables but if I am correct I have to map all the tables (there are 70ish) and likely have to adjust syntax so it doesn't do the overwrite either.

I looked at just scripting the changes but this could get mucky.

I would appreciate any assistance as I have already lost a fair bit of time exploring options and would really like to nail down approach asap!

Ideally I would like to schedule the job to do the restore on the bak and have it run early morning but it doesn't seem that simple. 

Please help

Answer 1

If I am right you are trying to sync two database records.but the two databases are in different servers.

The ways you can sync are

1.Using Replication

2.Using SSIS

3.Using LinkServer

If you are trying SSIS . You need to write a single SSIS package that compaitable for all tables. i.e if you pass the name of the table as a parameter the automatically take all the records or incremental records by date and store it in the other server in temporary table.Then compare the temp table with the particulare table.IF possible use the MERGE statement to SYNC and then remove the temp table.This package you can schedule in Job as per your need. 

---

ganeshk

Answer 2

Thank you for your response.  The databases currently live on the same server. 

Does this change the answer you have supplied?  Which approach would you advise if it needs to run daily on schedule?

This needs to be done for all tables in the database not just a specific record, all records for all tables.

Thanks

---

Answer 3

I would setup Snapshot Replication and schedule it to run once a day.  That will do what you are looking for.

Answer 4

Thank you for your response.

I tried setting up Snapshot Replication and scheduling but when it runs it seems to overwrite the subscriber version and I'm looking to keep some historical records that were already in it before the replication. (I did push from publisher).  I had hoped that I could configure it to keep the historical info as well instead of overwriting it.

I have successfully written overwrite process with jobs, ssis, bids and replication but can't seem to get it not to overwrite my historical.

I'm afraid to redirect to datawarehouse or custom temp table merges if it is something that can be adjusted in one of my earlier methods.

Any other ideas before I try ganeshk approach above?

Thanks!

Answer 5

The way to not overwrite historical data is to store it in another table, ie XXX_History.  Then create a view which UNION ALLs the XXX  with XXX_History.