[RESOLVED] Restore a previous versioned SQL DB to newer SQL Server

so as the title states. I need to restore or move a SQL 14 DB on to SQL 12 server. Its already populated with some data and has complex constraints, keys and functions etc. So its not allowing me to restore on SQL 12 as an error comes up stating its version 14 and needs to be 12 or older.

Google is an acquaintance and says I should script out the entire database and run the script on the older sql server but as many of us know with large complex databases it never runs correctly.

Upgrading or installing the newer 14 version is not an option at this time as this is in a production environment.

Any ideas?

You can go up from 12 to 14 but you can't go down from 14 to 12... unless you script out the DDL ad the data and run that (very carefully of course)...

Of course upgrading to 14 is an option, it just might not be a viable one.

-tg

Yeah, I think you're stuck with scripting this out I'm afraid. And the chances are the script won't just run smoothly on the target. You're going to have to debug it and remove any syntax/features that were introduced after 12. You might get lucky and have a smooth ride but you should probably go into this expecting some pain.

You can use SSIS to move the data but you can't just restore a newer to an older version... I would do the SSIS rather than scripting... You can add a conditional split to add new rows or update rows based on the key that way

Quote:

---

I would do the SSIS rather than scripting

---

I was thinking scripting to create the structure which would just be a case of RClick on the DB in SSMS and select Script Database as > Create.

You could probably build a DACPAC by selecting Tasks>Extract Data Tier Application and then import it to the new server using Import Data Tier Application. I'm not sure whether that'll let you go back versions though.

I hadn't actually considered the data but, yeah, a straightforward data pump built in SSIS would be ideal.

Quote:

---

Originally Posted by FunkyDexter

I was thinking scripting to create the structure which would just be a case of RClick on the DB in SSMS and select Script Database as > Create.

You could probably build a DACPAC by selecting Tasks>Extract Data Tier Application and then import it to the new server using Import Data Tier Application. I'm not sure whether that'll let you go back versions though.

I hadn't actually considered the data but, yeah, a straightforward data pump built in SSIS would be ideal.

---

Its shown that to use the scripting wizard which generates a more complete database script possibly?
Right click on database > Tasks > Generate Scripts... Then you have to change some Advanced setting to get it to include the data, set the format and a few other things I read.

Quote:

---

Originally Posted by techgnome

You can go up from 12 to 14 but you can't go down from 14 to 12... unless you script out the DDL ad the data and run that (very carefully of course)...

Of course upgrading to 14 is an option, it just might not be a viable one.

-tg

---

Yea cant upgrade to 14 as then its a major risk and issue with all the other production db's on the server

Quote:

---

Originally Posted by FunkyDexter

Yeah, I think you're stuck with scripting this out I'm afraid. And the chances are the script won't just run smoothly on the target. You're going to have to debug it and remove any syntax/features that were introduced after 12. You might get lucky and have a smooth ride but you should probably go into this expecting some pain.

---

Im already dreading it as we needed to switch the website over from dev environment to production at the drop of a hat when the owner said the contract was approved and we need it up asap to accept th third party to start inputting orders (data) :rolleyes:

Quote:

---

Originally Posted by GaryMazzone

You can use SSIS to move the data but you can't just restore a newer to an older version... I would do the SSIS rather than scripting... You can add a conditional split to add new rows or update rows based on the key that way

---

Havent used SSIS yet so with a semi time crunch I wont have the time to get up to speed on that. Its a limited amount of data in various tables so no worries exporting it out and inserting it back in

almost have it recreated on the production server with data imported. Just need to re-sync from this mornings activity and change the website to connect to the new db copy on production sql server. So far so good :)

So as of today I have the new copy of the database structure created and data ported over to the production database server.

Seems using the Generate Scripts wizard along with the tweaking of Advanced settings generated a trouble free experience (so far as the final test will be when users start using it later today).

Thanks for the help guys


MS should really make something for backwards compatibility as even if its generating scripts for the entire db and then running it against your target server. Why do I have to do the scripts to maintain "compatibility"? Seems it should be so easy for them to do. If it finds an incompatible feature or function then warn the user it wont be ported etc.