locked
Copying Databases RRS feed

  • Question

  • We are about to finish our build of a new network. Except for the databases, nothing else is being moved over. This was done intentionally as the old system started as an SBS Server log ago and never cleaned up. In doing so, the current network has a SQL Server 2012. The new network has SQL Server 2014. Both networks are on completely different networks and IP's.  In testing the new networks SharePoint sites, I backed up the Web Apps databases and restored them to the new server. Made some modifications to the sites which obviously changes the data in the database.  I want to keep these changes.

    So my question is, when I cut over this weekend, what is the best way to update the new databases with the changes I made with the old databases with the most recent data without having to do a Backup and Restore?

     
    • Edited by Bill Fry Monday, March 30, 2015 7:46 PM
    Monday, March 30, 2015 7:45 PM

Answers

  • Hi Bill,

    According to your description, you want to update the new database without using backup and restore. To achieve this, you could consider replication.        

    If there is a very high volume of insert, update, and delete activity and the data changes frequently, you could consider using Transactional Replication.

    However, if the data changes infrequently and there are small volumes of data to be updated, you could consider using Snapshot Replication. The setup steps are very similar to Transactional Replication.

    Regards,
    Michelle Li
    • Edited by Michelle Li Wednesday, April 1, 2015 1:29 AM
    • Proposed as answer by Michelle Li Thursday, April 2, 2015 5:56 AM
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 7:34 AM
    Tuesday, March 31, 2015 9:50 AM

All replies

  • You'll have to script a MERGE command for every table, updating matching records, inserting records not matched by destination, and deleting the ones not matched by the source.

    You can also use an integration services data flow along with SCD transformation to the same end, but performance will be reduced.


    Just because there are clouds in the sky it doesn't mean it isn't blue. But someone will come and argue that in addition to clouds, birds, airplanes, pollution, sunsets, daltonism and nuclear bombs, all adding different colours to the sky, this is an undocumented behavior and should not be relied upon.

    Monday, March 30, 2015 8:18 PM
  • Hi Bill,

    According to your description, you want to update the new database without using backup and restore. To achieve this, you could consider replication.        

    If there is a very high volume of insert, update, and delete activity and the data changes frequently, you could consider using Transactional Replication.

    However, if the data changes infrequently and there are small volumes of data to be updated, you could consider using Snapshot Replication. The setup steps are very similar to Transactional Replication.

    Regards,
    Michelle Li
    • Edited by Michelle Li Wednesday, April 1, 2015 1:29 AM
    • Proposed as answer by Michelle Li Thursday, April 2, 2015 5:56 AM
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 7:34 AM
    Tuesday, March 31, 2015 9:50 AM
  • Hi Bill,

    According to your description, you want to update the new database without using backup and restore. To achieve this, you could consider replication.        

    If there is a very high volume of insert, update, and delete activity and the data changes infrequently, you could consider using Transactional Replication.

    However, if the data changes infrequently and there are small volumes of data to be updated, you could consider using Snapshot Replication. The setup steps are very similar to Transactional Replication.

    Regards,
    Michelle Li

    It is a one-time only synchronization, he is moving his database instance to a new server, and, having restored the databases on it, data on the old server was updated.

    Just because there are clouds in the sky it doesn't mean it isn't blue. But someone will come and argue that in addition to clouds, birds, airplanes, pollution, sunsets, daltonism and nuclear bombs, all adding different colours to the sky, this is an undocumented behavior and should not be relied upon.

    Tuesday, March 31, 2015 2:08 PM
  • It is not supported to directly modify the tables in a sharepoint database. Why not backup/restore again?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, March 31, 2015 5:41 PM