How to attach Database created in higher version to lower version of SQL Server
-
I have attempted to attach a database created in a higher version of SQL Server to a lower version, but I encountered errors. I also tried the backup and restore method, which resulted in errors as well.
According to the following link, I explored several options but was unsuccessful:
https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/
1. Generate Scripts Wizard in SQL Server Management Studio: This option is not suitable because it has to be done manually
2. SQL Server Integration Services: This feature is not available in the Express edition of SQL Server.
3. Custom Scripting and BCP: I already have a copy option in my code.
I have also tried this below from below link didn’t work
MSSQL-Scripter Tool as an alternative for Generate Scripts
Given these limitations, I am looking for .NET code or SQL Queries that can help me attach a database created in a higher version to a lower version of SQL Server.
-
It would be helpful to know what the higher and lower versions of SQL Server are.
Note that you could use SSIS to move data from database A to database B by working in debug mode, from within VS. In which case, the process would be
- Create a blank database on server B
- Apply the database schema to the blank DB
- Use SSIS to copy data from A to B
If there are many tables and lots of FK constraints, part (3) will be a pain, because the import will have to be done in logical order, unless the constraints are dropped and then recreated afterwards.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Going backward over version(s) is always a hard time.
As you've figured out, the only way is a scripting and data migration solution.
On the other hand, since sqlexpress is free, why would you stick with an old version ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If, and this is a big one, there are zero data types, indexes, settings on any of those, that are incompatible between versions, this will not work.
What you can try is the bacpac. It's basically the same as a dump. You'll have to disconnect everyone from the original database because this method is not transactionally aware, so any data in flight will cause it to fail. From there, the easy way, is to right click on the database in SSMS, select Tasks, and Export Data Tier Application. NOTE, Export, not Extract. Why they named to two different processes so closely I'll never know, Microsoft. Anyway, once it's out to a file, connect to your second server and Import the Data Tier Application (bacpac).
ZERO guarantees this works. But it's usually your best bet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply