June 12, 2014 at 3:06 am
hello,
I have a back up file which is generated from sql server 2008 R2 and I need to put it in sql server 2012. In some blogs I read that any file generated in a lower version cannot be used with its higher version. But I guess there must be some solution or work around form this issue.
Any help is appreciated.
Thanks,
Manoj
June 12, 2014 at 3:15 am
Manoj
You can restore a backup made with SQL Server 2008 R2 on a SQL Server 2012 server. Don't forget to change the compatibility level on the database to that of the new server.
John
June 12, 2014 at 3:27 am
Yep, you can just restore it. You can't go the other way round (e.g. 2012 to 2008)- maybe that's what you were thinking of.
June 12, 2014 at 3:34 am
The issue is around even older versions of SQL Server. You can't take a SQL Server 2000 backup and restore it to 2012. You have to convert it to 2005 or 2008 first. I think the same thing applies to 2005 to 2014. But just hopping a single version, as has already been pointed out, that works just fine.
"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 12, 2014 at 10:18 am
As other posters have stated, a 2008 (+R2) or 2005 backup is restorable to Sql Server 2012. The database will be upgraded to Sql Server 2012 when you restore it, no matter what compatibility level you select. At this point, no backup or detach operation will allow you to restore or attach the database to prior versions of Sql Server, again, no matter what the compat. level. So this is a one way operation(unless you're willing to export and import to other versions, but that's a different operation.)
Anyway, restoring the 2008/R2 database to 2012 won't fix any issues such as desupported features that were used in stored procedures, functions, etc. Neither does setting the compatibility level to 2012.
Setting the compatibility level to 2012(110) before you've corrected any upgrade issues in the database (one example might be stored procedures that use syntax that's been discontinued) will result in problems. In the case of stored procedures, they will fail when invoked typically. The failure may take the form of ABENDing and raising an error, or quietly returning incorrect results (depending on the issue).
This is why I would recommend against immediately upping the compatibility level, unless you:
1) Aren't immediately going to try to put this into production.
2) Or you know there are no issues. I mean really know. Even so, there's probably at least one issue hiding somewhere.
2) Or are ready to correct the issues immediately.
3) Or accept that some things may just be borked until someone gets around to correcting the issues.
[As an aside, I used to work for someone who would flip her wig when she came across a database set to an older compatibility level, but she had no clue about compatibility level and it's effects, despite being the DBA. To her it was JUST WRONG,WRONG,WRONG to have database set to an older compatibility level. So she would change it, or insist it be changed and then things would break since no one had an opportunity to correct the issues yet. Now to be fair, she generalized this behavior to everything though (while complaining about what a numbskull I am) so it wasn't ONLY this. Which led me to quit that job, when it became clear she wan't retiring.]
June 12, 2014 at 10:00 pm
This was removed by the editor as SPAM
June 12, 2014 at 10:15 pm
You can definitely restore the databases that are created in SQL 2005/2008/2008 R2 to SQL Server 2012. The issues is other way around you cannot restore SQL 2012 database on a lower editions.
One issue with backups taken in SQL 2005/2005/2008 R2 is even though we change compatibility mode. The depreciated feature wont work after the restore in case if you are using in your database.
September 6, 2014 at 11:55 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply