Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DrakeFrost
Starting Member
7 Posts |
 Posted - 2011-12-06 : 16:10:48
|
| I believe I am in a very unusual situation... I am working on upgrading an application that uses SQL server as a back end from SQL Server 2005 to SQL server 2008 R2. This also will be an upgrade of the application itself, which makes some changes to the database objects.My question is, what is the best way to capture the changes that have happened since the 2005 DB was backed up, adn import them into the newly upgraded DB instance? Or is there no way to achieve that kind of delta, and I just have to take a new backup when we are ready to cut over to teh new app, adn re-upgrade everything?What are your thoughts?Thanks.DrakeFrost |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-06 : 16:19:19
|
| Yes there are ways to get the deltas, however that will be very time consuming. Instead, I'd recommend a new backup when you are ready to do the switcheroo. I typically restore a full backup hours before the upgrade, restore it with norecovery, and then add a diff+tlog chain or just the tlog chain. This allows me to switch to the new system with very little downtime. I can typically complete the migration in under a minute.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
DrakeFrost
Starting Member
7 Posts |
Posted - 2011-12-06 : 16:33:58
|
| I see. the only issue that will present itself in my situation is the application... It stores its version in teh DB itself. Our current production instance is set up for version 9.x of the app, and the new instance is for versoin 10.x. and 10.x will not access a database that is at version 9.x. So restoring a new backup will basically revert the DB back to a version that will render teh app unusable unless I go back through all the upgrade process again. Granted, I have a procedure that I can follow, and it should only take about 1.5-2 hours (depending on how fast the installer can upgrade the database... Thanks for teh quick response, Tara.DrakeFrost |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-09 : 03:06:34
|
| I may be answering the wrong question, but can you script the changes to the database (between 9.x and 10.x) and then apply them to the 9.x database to upgrade it?Tools such as Red Gate DB Compare will generate the script for you (you jsut tell it where to find a 9.x and 10.x version and it will generate a delta change-script. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-10 : 07:19:50
|
| Yup, you are right, I see that now thanks.Why does it take 1.5 - 2 hours? Maybe that's something that could be "improved"? Would help to know what it does.Delta script of data might need to be applied "through" the update script too .. as that might have changed the "nuance" of the data.We do:Make changes on DEV system.Script all the changes that were made during the DEV processTo upgrade we: apply delta-script to QA / TEST / PRODUCTION databaseIf that is what you are doing that takes 1.5 - 2 hours then explain the process and we'll see if we can suggestion improvements.For example, one thing we do is to upload the script to the server itself, and run it there, rather than running it "across" the WAN - which saves quite a bit of time (our scripts are huge!)Maybe some of your changes are "benign" and could be applied whilst the system is LIVE (before and/or after the main upgrade), and then the parts of the script that interfere with the application could be applied during scheduled downtime. e.g. maybe some indexes could be created with the system LIVE. |
|
|
|
|
|
|
|
|
Merging changed data between SQL DBs