Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, January 24, 2013 6:13 PM
I am setting up a new SQL Server 2012 trying to move data via backups from a SQL Server 2008. The 2012 server says the backup data is not compatible. What is the recommended method to do this?
Many thanks...
Todd
Friday, January 25, 2013 4:28 PM ✅Answered
The Source SQL Server is Version 8.00 194 using
Hello Todd,
Now it's clear for me; in your first post you wrote, the backup comes from a SQL Server 2008, but that backup comes directly from a SQL Server 2000 = 8.00
You can't restore a SQL Server 2000 backup to a SQL Server 2012. Only the last 3 version are supported for backward compatibility, means on a SQL Server 2012 you can restore backups of version 2008R2, 2008 and 2005, but not 2000. You have to restore your backup first to a e.g. SQL Server 2008 and then you have to create a new backup of this database; then you can restore this new backup from 2008 to 2012.
Olaf Helper
Thursday, January 24, 2013 6:19 PM
Hello Todd,
You can move database with backup/restore or with detach/attach method fro SQL Server 2008 to a SQL Server 2012 without any problems.
When you get an error message regarding compatibilty issue, then because you are trying it in the other direction; from SQL Server 2012 to the lower version SQL Server 2008; that's not possible.
So are you really trying the way 2008 to 2012 or is it backward 2012 to 2008?
Olaf Helper
Thursday, January 24, 2013 6:30 PM
AFAIK, what you are doing is right. is the sql 2008 backup a native backup?? could you please post the exact error message.
Hope it Helps!!
Thursday, January 24, 2013 11:50 PM
Hi,
Despite your database is hosted on a SQL Server 2008 instance, it looks like it's compatibility mode is set to SQL Server 2000 or lower.
In SSMS, right-click on your database, select Properties and then have a look at the Options.
If I'm right, first modify your compatibility mode to SQL2005 or higher before backing it up again.
Be careful, try it first on a staging server and test that your application is still OK before doing that on the production server.
JN.
Jean-Nicolas BERGER
http://blog.sqlserver.fr
Friday, January 25, 2013 9:21 AM
As olaf said that might be the problem...
In addition to that you can use export/Import method or BCP,Copy Database Wizard as well..!
you cannot do restore like higher to lower version but you can script the database objects and can restore later.
but good option would be Backup/restore.
Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.
Friday, January 25, 2013 11:09 AM | 1 vote
it looks like it's compatibility mode is set to SQL Server 2000 or lower.
Hello Jean-Nicolas,
Good point, I run a test on it: SQL Server 2005, created a new database with compatibilty level 80 = SQL Server 2000, backup and then restore on SQL Server 2012: Worked without any problem, the comp level has changed to 90 = 2005 automatically, the lowest supported comp level on SQL Server 2012.
Reason: The comp level is may 2000 or lower, but the database format is already 2005, so SQL Server 2012 can upgrade the database without any problem.
So the error must have an other reason.
Olaf Helper
Friday, January 25, 2013 4:02 PM
The Source SQL Server is Version 8.00 194 using
Database compatibility level 80
Here is the error message:
And here is the screen for the Restore (I am able to use the backup file to restore the Source...did that to test it):
Todd
Friday, January 25, 2013 4:48 PM
I guess that is what happens when one sleeps while the world moves on. Now to find SQL Server 2008...since Windows 8 does not allow anything other than 2012.
Many thanks...
Todd
Friday, January 25, 2013 6:02 PM
Hello Todd,
Please have a look at MS Support: Using SQL Server in Windows 8 and Windows Server 2012 environments
There are some known issues installing SQL Server 2008/2008R2 on Windows 8, but is possible to install it (I didn't tried it, yet)
If your database size is less then 10 GB, you could use the free SQL Server 2008/2008 R2 Express Edition to migrate your database first to 2008/2008R2. Good luck and please give a brief feedback, when you successfully migrated your database; thanks.
Olaf Helper