Today I am writing on the topic which I do not like to write much. I enjoy writing usually positive or affirmative posts. Recently I got email from two different DBA where they upgraded to SQL Server 2005 trial version on their production server and now as their trial version was expire they wanted to downgrade their database to previous licensed version they had.
The main questions is how they can downgrade the from SQL Server 2005 to SQL Server 2000?
Answer is : Not Possible.
There are no tools or native SQL Server facility which does this. I am also not aware of any of the tool available from third party software vendors. Database (.mdf, .ndf, .ldf) can not be restored from higher version to lower versions. If you want to downgrade your server, fresh install of previous version of server should be done first. Right after that recreate all the schema to create empty tables and database objects (SP, UDFs etc). Use SSIS/DTS or any other import/export utility and import data from upgraded version to downgraded version.
First of all I am true believer of licensed software. I do not like to use or encourage use of pirated software. Again, I do not like to even downgrade to lower version. SQL Server 2008 and SQL Server 2005 have many new and advance features over SQL Server 2000. One must keep on moving towards new technology and upgrade themselves and company technology.
It is responsibility of the each developer to encourage technological advancement and prevent piracy.
Reference : Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Seems odd to me. If there was any risk of not purchasing the server when the trial was over, I wouldn’t use it anywhere of any concern. That given, you can install SQL 2000 on another machine and use a tool like Red Gate SQL Compare and Red Gate SQL Data Compare to bring across all of the data and schema, provided you aren’t doing anythign that SQL 2000 can’t do. Still, if you have the budget, 2005 is well worth it…
I’m sorry, but if a business is silly enough to allow one of its’ “techies” to install trial software onto a production servers, then it must pay the price for that incompetence.
The best advice I can give to the individuals concerned is to get another job before your boss finds out what you’ve done.
I have been searching for a way to create a SQL 2005 compatible database using SQL Server 2008. I see in this post that it is not possible to downgrade from 2005 to 2000 – but has this changed in SQL 2008? I am a student in Web Development .NET and writing code using Visual Studio 2005. I need to create a database but only have SQL Server 2008 since it is free to students from the MS Dreamspark website. I creat the database in SQL Server 2008 and load some data – and everything is ok. Howevet Visual Studio 2005 cannot seem to see or recognize this new database. When I create a database in Access 2007 I have the same problem -except Access does allow me to create a database with a previous version.
Does anyone have any suggestions? I have soent so much time trying to load 10 records into a file that I can use in a VB program that I just dont know what to do. Access datatypes are a bit confusing and I love the control possible when using SQL.
SOS! Thanks everyone!! Im SUCH a newbie but a very enthusiastic new programmer with a 4.0! I hope to be able to help others in the online forums in the years to come.. but right now I am lost in the differences between software versions for database creation.
All the best!
Ann
@Ann
I am not sure if this will work, but definitely you can try this.
Create database in SQL Server 2008.
Once you create database,
go to SQL Server Management studio,
Object Explorer,
Expand Server ,
Expand Databases,
Right Click database Name you just created Click Properties,
Click Options,
Set Compatability Level to : SQL Server 2005 (90) from drop down list.
Click OK.
This should set your database as if it exists in SQL Server 2005. Try this if it does not works. Leave a comment. We will try some thing else.
~ IM
If i were to have a database in SQL 2008. How can i create a backup of it and restore it onto 2005 .
The above method That Imran does not work ?? any other ideas ???
Hi there,
I have the same issue, I recently received some backups from sql2008 and I’ve restored them in an 2k8 server, from that point I’ve changed the compatibility level from 100 to 90 then performed a backup and tried to restore in 2005 and guess what…it errored. I’ve also tried to copy_database trough the wizzard and that errored too with the same compatibility level. The next thing I tried was to export data from tables and at some computed rows from a few tables ERRORed, of course….so, I dont think this could be possible.
I have had to downgrade from 2008 to 2005 a couple of times due to various reasons such as client insistence. Redgate’s tools absolutely saved me – this is the only way I know of I had spent hours researching it. Good luck!
I can explain to you one case where it’s usefull to have a downgrade. In this scenario, you detach, attach from 2005 to 2008 without any problem. after a while, problems arises and now, you need to rollback. What can you do ? With similar version, you would simply detach, attach back to the original server. Now, you can’t !
The solution…
We reattached the original DB and used Data Compare from Red-Gate. Of course, the downtime was Wayyyy longer but we could make it. In a critical environment, it would be UNACCEPTABLE.
Dudes, script it out. structure and data. You might have to scan through looking for a couple of things (eg date data type, change to datetime), but I’ve done it.
Scanario was dev machine vs prod. TPTB didn’t want to upgrade prod, so we had not choice.
Yes, there are many dev to prod machine scenarios. I think it is nonsense that there is no way to downgrade the files.
I really wonder about the silly comments some user leave here. Such as the one from “Graham Smith”. This Graham Smith seems to be himself to be a little incompetent in using his brain to imagine cases where there is a need to convert backwards. Whatever these reasons are. This is not the interest, it isn’t proactiv, it is only distructive. Please stay by the topic and leave own disadvantages in thinking out of here. So back to topic:
Of cause there are always ways to convert a database.
1) Tools like SQLScriptor,…(this is not the only one! May cost sth)
2) Create a script: (i did a 1Minute search: Follow instructions:
) I favorite this one. It worked for me several times!
3) Use Import/Export if you want only table-content moved.
Please don’t tell that you specialists are not be able to do such conversion. Good luck