June 9, 2014 at 2:31 am
Hi Experts,
Can we upgrade SQL server 2000 DB's to Sql server 2012 directly? I tried restoring 2000 .bak in 2008 R2 but i am getting compatible errors. Should i have to migrate it to 2005 first and then to 2008 and later to 2012?
Thanks in Advance.
Regards,
Vijay
June 9, 2014 at 2:41 am
Vijay
There's no direct upgrade path from 2000 to 2012 - you have to go via 2005 or 2008. You don't have to go through them both, though.
John
June 9, 2014 at 4:19 am
Hi John,
I tried restoring it in 2008 R2. But i got the same compatibility error.
Regards,
Vijay
June 9, 2014 at 4:39 am
Vijay
I'm sorry, I misread your question. I thought you were asking about upgrading the server. You should be able to restore a 2000 database on 2012. What is the exact error message, please?
John
June 9, 2014 at 4:46 am
It could be you don't have the right service packs in place on the 2000 version to upgrade to 2008R2. Do a quick search for the Upgrade Advisor from Microsoft. That will tell you what compatibility issues you might have to get the upgrade done.
And no, you don't need to go through all the versions. You should be able to get to 2008 from 2000 and then go to 2012.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2014 at 12:12 pm
vijay.singh 14112 (6/9/2014)
Hi Experts,Can we upgrade SQL server 2000 DB's to Sql server 2012 directly? I tried restoring 2000 .bak in 2008 R2 but i am getting compatible errors. Should i have to migrate it to 2005 first and then to 2008 and later to 2012?
Thanks in Advance.
Regards,
Vijay
From personal experience.... after migrating dozens of databases running on SQL 2000 to SQL 2008... do a side by side migration.
Backup the SQL 2000 database and restore on the new environment. Or create the objects and migrate the data via SSIS.
SQL 2000 is way, way different and there are a lot of stuff, besides permissions, statistics, etc, that run different.
Also, you should test this a lot! Even if it goes well, there is a chance that the T-SQL code will break. There is SQL 2000 syntax already deprecated. I remember spending hours trying to fix a problem back in 2008. It was due a old fashion way of using joins on SQL 2000. Developers did not test that part.
June 10, 2014 at 12:20 am
Hi John,
Please find the error message below.
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Regards,
Vijay
June 10, 2014 at 1:32 am
Hi,
You may also try detaching the databases and copying the mdf & ldf databases to the new environment and re-attaching them.
That usually works, but obviusly you will require downtime for that.
but i prefer using sp_detach_db & sp_attach_db command, you can do this also by going to Enterprise manager on SQL 2000.
June 10, 2014 at 1:46 am
vijay.singh 14112 (6/10/2014)
Hi John,Please find the error message below.
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Regards,
Vijay
Version 11.00.2100 is SQL 2012, as stated before you cannot migrate directly from 2000 to 2012. Have a look here for further resources: http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx
June 10, 2014 at 2:04 am
vijay.singh 14112 (6/10/2014)
Hi John,Please find the error message below.
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Regards,
Vijay
Vijay
That's strange. I thought I'd restored 2000 on to 2012 before, but maybe I haven't. I don't know whether you'd have a better chance if you upgraded to 2000 SP4?
John
June 10, 2014 at 4:42 am
John Mitchell-245523 (6/10/2014)
vijay.singh 14112 (6/10/2014)
Hi John,Please find the error message below.
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Regards,
Vijay
Vijay
That's strange. I thought I'd restored 2000 on to 2012 before, but maybe I haven't. I don't know whether you'd have a better chance if you upgraded to 2000 SP4?
John
Nope. For good or for ill, you can't go straight from 2000 to 2012. You have to go to 2005 or 2008 first. It's a pain, but Microsoft has been warning us about it for years. I'm pretty sure that you now can't go from 2005 to 2014, let alone 2000 to 2014. Have to love the two year release cycle.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply