Rstoring Database from Sql Server 2008 to Sql Server 2012

  • mp5387

    Right there with Babe

    Points: 794

    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

  • sql-lover

    SSCoach

    Points: 18531

    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.

  • mp5387

    Right there with Babe

    Points: 794

    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

  • sql-lover

    SSCoach

    Points: 18531

    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.

  • hcip.77

    SSC Eights!

    Points: 957

    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.

  • udaynov17

    Mr or Mrs. 500

    Points: 542

    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.

  • Summer90

    SSC-Dedicated

    Points: 32821

    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.

  • sqlnyc

    SSCommitted

    Points: 1726

    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:

    80 SQL Server 2000 SQL Server 2008 through SQL Server 2008 R2

    90 SQL Server 2005 SQL Server 2008 through SQL Server 2012

    100 SQL Server 2008 and SQL Server 2008 R2 SQL Server 2008 through SQL Server 2014

    110 SQL Server 2012 SQL Server 2012 through SQL Server 2014

    120 SQL Server 2014 SQL 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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply