March 4, 2015 at 12:12 pm
Hi,
I have a database backup from sql server 2008. Its version is 10.50.1600.1 .( I name this server, server I)
Can I restore it on another server ( I name this server, server II ) which has sql server 2012 without any update, or migration?
Or It better if first upgrade sql server 2008 to sql server 2012 then provide backup from that, then restore it to sql server 2012 on server II?
which one is better?
More Appreciated,
MP
March 4, 2015 at 3:25 pm
Yes
You can restore from a lower version to a higher version. You cannot restore from higher version to lower.
You may have to change database compatibility level after the restore so it will behave as newest version.
Some TSQL statements and/or DMVs may not work properly if those were removed in SQL2012.
So test a lot before pointing your app to the new instance.
March 4, 2015 at 4:58 pm
Hi Sql-Lover,
Thanks for your answer. Do you mean after restoring my database from SQL 2008 on SQL 2012, I should change compatibility level on server 2012 to "120" ?
Many Thanks,
MP
March 4, 2015 at 8:07 pm
mp5387 (3/4/2015)
Hi Sql-Lover,Thanks for your answer. Do you mean after restoring my database from SQL 2008 on SQL 2012, I should change compatibility level on server 2012 to "120" ?
Many Thanks,
MP
Yes.
March 4, 2015 at 9:22 pm
IF you are going for Instance level means it upgrade all databases which existed on SQL 2008 to SQL 2012
IF you go for database level then You need to move User,SQL jobs, Update your Packages also need to include in migration plan etc....
It is up to you what suit to your environment best and How you want to proceed.
As sql-lover mentioned in his thread. test everything before you will go for migration or upgrade.
March 9, 2015 at 2:15 pm
SQL 2012 version compatibility level is 110. Please check.
For your situation you can restore a database from sql 2008 to sql 2012. then change the compatibility level to 110. If you use SQL 2014 then your compatibility must be changed to 120.
Ensure that there are no orphan users after doing a restore and also check that the owner of the database if it is the expected or needed one.
March 10, 2015 at 7:45 am
Other things to do after moving db from SQL2008 to 2012...
run dbcc checkdb with data_purity
also, at bare minimum, run sp_updatestats on the db. It is best to rebuild all of the indexes. This will ensure that the SQL2012 optimizer will get accurate access paths to the data for changes in the optimizer/stats from SQL2008.
March 17, 2015 at 7:12 am
Also note that the compatibility level of a database is not necessarily the same as the version of SQL Server that it is hosted in (please forgive formatting errors in what follows).
Level Version Can be executed in:
80SQL Server 2000SQL Server 2008 through SQL Server 2008 R2
90SQL Server 2005SQL Server 2008 through SQL Server 2012
100SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 through SQL Server 2014
110SQL Server 2012SQL Server 2012 through SQL Server 2014
120SQL Server 2014SQL Server 2014 through SQL Server 2014
So you could be running 80 compatibility in SQL 2008, but when you restore to SQL 2012, the compatibility level will be automatically upgraded to 90 (the minimum compatibility level supported). It will do this "silently". Upgrading the compatibility level can involve "breaking" changes which could affect your applications, and should be thoroughly tested.
sqlnyc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply