Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using. Both Databases will be using the same tables and have the same fields, the difference is in the data. Ideally I would like to do this through SQL and not have to get a third party application. Any ideas or suggestions, I'm kind of in a bind here.

share|improve this question
I'd be surprised if there is a product out there to do this. Think about it, you have db1 and db1' assuming only data values have changed, what is correct? People table has Mary Smith in db1 and Mary Winklebauer in db1' - which one should be retained? How would a generic program implement that merging of data? If all your tables have the concept of last modified date, then you can probably figure out which row to preserve. Either way, you'll need to restore the backup to a different catalog and merge that data into target in FK reference order. – billinkc Jun 13 '12 at 18:05
1  
Does it have to be if the user wish? Can it be continously merging the data? – Martin Sjöberg Jun 13 '12 at 18:07
Is it every table or just a select few? You could take your backup now, restore it under a different catalog name and set it to readonly. Then write the merge process (three part naming with merge statements, some SSIS, or some other option other folks will come up with) and let it sit idle until they have need to actually merge the data. – billinkc Jun 13 '12 at 18:17
To be honest I questioned the reasoning for the "backup to merge" myself. I think @bilinkc is right in the statement of what data is kept and what data is overwritten. I think what I am going to have to do is when they "restore" the old database to restore it with a different name and set it as read only as suggested. – Jimmy Jun 13 '12 at 18:38

migrated from stackoverflow.com Jun 13 '12 at 17:52

2 Answers

up vote -5 down vote

You are looking for Merge Statement

Merge dbo.Table1 as target
using   dbo.SourceTable as source
on      target.ID = source.ID 
when    not matched by target then
        insert (id, ColumnName) values (source.id, source.ColumnName)
when    matched then
        update set col1 = source.col1, col2 = source.col2;
share|improve this answer
4  
MERGE is for combining the data in separate tables, not databases... – Aaron Bertrand Jun 13 '12 at 17:58
after restore, can i use these two db and merge data to synchronize it? – abcdefghi Jun 13 '12 at 18:01
Currently the only way to restore the old database is to delete the current one, but I do not want to lose the data that is in the current database. @Kanavi thank you for the responce but I believe Aaron is right, the MERGER functionality is for specific tables not whole databases. – Jimmy Jun 13 '12 at 18:04
up vote 1 down vote

If you have Visual Studio Ultimate or Premium, you can use "Data Compare" feature in Data menu. It can compare data between two tables and have ability to directly update your database or generate update script.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.