Home / server / Questions / 500480
Accepted
Aaron Mason
Asked: 2013-04-18 23:06:34 +0800 CST

Restore backup from SQL 2008 to 2008R2 with standby

  • 772

We are looking to replicate our production database to another instance so that when reports are run, it doesn't slow down the application to the point of being barely usable. I had limited success with SQL Server Replication, but as the application fiddles with the data model from time to time, this approach was unsuitable.

When we spoke to the vendor (who told us that yes, it could be replicated) he revealed that their other clients use log shipping, which is my current direction. My only problem is the server runs SQL 2008, whereas the target server runs 2008R2. When I try to restore a backup in standby, it falls flat on its face, but if I use another instance that is the same version, the process runs flawlessly.

Is there a way to do it with different SQL Server versions, or am I stuck with the current arrangement?

Thanks

sql-server-2008

1 Answers

  1. Best Answer

    Technically, you could write an update script that copies the new data from the production to the test system but not only is that going to be complex, you're also going to run into problems that are far from trivial to solve:

    • Performance on BOTH DB will be impaired during the copy.
    • New rows are easy to detect but deleted/updated rows aren't. If the application doesn't already implemented some kind of data versionizing, you're going to have to implement it yourself or copy the content of all tables every time.
    • It will be hard to maintain a globally consistent state in the target DB with wrapping the update mechanism in one giant transaction (on both side). I let you guess what the consequence of that will be :P

    Alternatively, you could also modify the source application so that it will write to both DBs at the same time (I used that solution in a similar case).

    Frankly, I can't see any (reasonable) solution that would be simpler than making sure that both DB are using the same version of SQL server.

    • 1