January 26, 2016 at 6:16 am
We have plan to migrate a database from sql server 2005 to sql server 2012 as part of data center movement. If it runs for a day and so many updates happened throughout the day and then business decides to rollback the migration and want to continue with old database 2005, how do we rollback then as backup from upper version and restore it at lower version is not allowed. Please suggest.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
January 26, 2016 at 6:27 am
Read it:
"Script out the objects from the newer database and then usp a bcp process to extract the data from the newer database and import it into the older database.
Use the SQL Server Import and Export Wizard to build an SSIS package to move the data (it will move the data only).
Build a custom SSIS package to do the data move.
Use replication to move the data from the newer database to the older one.
Use some other form of scripting, such as with PowerShell, to keep the databases in sync."
January 26, 2016 at 6:29 am
You can't restore a database from a higher version of SQL Server to a lower version. There are various properties/structures at each version that are not recognised in the lower versions.
Probably the best way to do this would be to move the data via BCP or an SSIS package.
You can't use replication to keep the old version up-to-date, because they're more than two versions apart. I don't know if the same restriction would apply to mirroring, but I suspect that wouldn't be possible either.
January 26, 2016 at 7:14 am
BrainDonor (1/26/2016)
You can't use replication to keep the old version up-to-date, because they're more than two versions apart. I don't know if the same restriction would apply to mirroring.
Mirroring requires that the DB be restored to the mirror to start, so it's not a 2-version limit, it's an up-only limit. You can mirror from 2005 to 2012, but not from a 2012 principal to a 2005 mirror
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2016 at 8:58 am
GilaMonster (1/26/2016)
BrainDonor (1/26/2016)
You can't use replication to keep the old version up-to-date, because they're more than two versions apart. I don't know if the same restriction would apply to mirroring.Mirroring requires that the DB be restored to the mirror to start, so it's not a 2-version limit, it's an up-only limit. You can mirror from 2005 to 2012, but not from a 2012 principal to a 2005 mirror
Of course it does, and I've created mirrors enough times that I should have remembered that. Thanks Gail.
January 27, 2016 at 7:16 am
Maybe a silly question, but are you able to spin up a test SQL 2012 box, put a copy of your data on it, and test against that for awhile? You should find out pretty quick if you are going to have any issues with SQL 2012.
The Redneck DBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply