Replace SQL Server Database

Question

A vendor has a data database (read only) that gets sent to us via dvd every week. Their upgrade script detaches the existing copy of the database, overwrites the MDF and LDF, drops all the users and recreates what they think proper security should be. Is there a way that I can just synchornize the data without taking the database offline? This is a 24/7 facility that causes 15 minutes of downtime during the updates.

Auxilary Information: The database has ~50 tables with a total size of 400 MB. The actual amount of changed data is somewhere around 400kb. Server is running Server 2008 with SQL Server Enterprise Edition 2008.

Answer 1

Read up on Red Gate Data Compare

http://www.red-gate.com/products/SQL_Data_Compare/index.htm

This will generate a script of differences for you that you can apply to the existing database.

This also has the ability to automatically synchronize your data

You will have to load the incoming database to a server for this operation.

Answer 2

Something you can do is to have two databases DB_A and DB_B when they send you the new DB you install it and replace DB_B. In the meantime all your users are using DB_A. Then rename the DB_A to DB_C and rename DB_B to DB_A. That will decrease the downtime to almost 0. Or you can just change the connection to point from DB_A to DB_B once the DB is ready.