none
Move DB from Server 2008 to 2016

    Question

  • Hi,

    Currently my SQL2008 server is a VM, i want to move and mount the hard drive containing the database to the SQL2016 VM machine.

    Will this work?

    Is yes, will the database work smoothly with SQL 2016 ?

    Ty,

    Sid

    Thursday, January 12, 2017 3:23 PM

Answers

  • Hi,

    Currently my SQL2008 server is a VM, i want to move and mount the hard drive containing the database to the SQL2016 VM machine.

    Will this work?

    Is yes, will the database work smoothly with SQL 2016 ?

    Ty,

    Sid

    Moving and mounting hard drive can be done and you must ask about that to Vmware admin. As for SQL Server provision a new VM install Supported windows OS and then install SQL Server 2016 now move database by backup and restore. Move logins and jobs by scripting out.

    If you are a seasoned DBA it should work smoothly what should be your concern is

    1. Have you run SQL Server 2016 upgrade advisory(UA) and sis it pointed any breaking changes. This is must dont proceed unless you have no warning or error messages from UA

    2. If there is warning or error message fix it or make sure how to take care of it after database is restored on SQL server 2016

    3. You might face CE regression on new version of SQl Server 2016. Means query running on 2008 might take more time on 2016. To solve this either tune queries use compatibility level 110 so that old optimizer is used


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, January 12, 2017 4:15 PM
    Moderator

All replies

  • Hi,

    Currently my SQL2008 server is a VM, i want to move and mount the hard drive containing the database to the SQL2016 VM machine.

    Will this work?

    Is yes, will the database work smoothly with SQL 2016 ?

    Ty,

    Sid

    please make sure all the components that will work on 2016 before moving production. 

    you can migrate to SQl 2016 either attach/deattach or backup and restore. that has their own advantages.

    Click here

    Thanks


    Thursday, January 12, 2017 3:37 PM
  • I think nobody can say "yes" or "no" on the fly.

    You need to perform your tests. At least run Data Migration Assistant against your SQL Server 2008 databases and see if there's any possible problem with the migration.

    https://www.microsoft.com/en-us/download/details.aspx?id=53595


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 12, 2017 3:47 PM
  • Hi ,

    you use can  Backup and restore or attach and detach method.

    Move report server database

    compatibility level must 130 in MSSQL 2016

    ALTER DATABASE database_name   
    SET COMPATIBILITY_LEVEL =  130



    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Thursday, January 12, 2017 3:57 PM
  • With SQL 2016 SP1 the migration should work. There may be issues with deprecated features. Your best bet it so run the upgrade advisor against the existing SQL 2008 server to see them. Now the upgrade advisor will not be able to detect deprecated features in dynamic SQL.

    I take it you are doing this in a test/uat environment as it is not wise to migrate production databases to new editions without extensive regression tests.


    BTW - moving databases will upgrade them and you will be unable to mount them on the original SQL 2008 instance after migration. You should test your backups to ensure you can go back if the need be.
    Thursday, January 12, 2017 4:15 PM
  • Hi,

    Currently my SQL2008 server is a VM, i want to move and mount the hard drive containing the database to the SQL2016 VM machine.

    Will this work?

    Is yes, will the database work smoothly with SQL 2016 ?

    Ty,

    Sid

    Moving and mounting hard drive can be done and you must ask about that to Vmware admin. As for SQL Server provision a new VM install Supported windows OS and then install SQL Server 2016 now move database by backup and restore. Move logins and jobs by scripting out.

    If you are a seasoned DBA it should work smoothly what should be your concern is

    1. Have you run SQL Server 2016 upgrade advisory(UA) and sis it pointed any breaking changes. This is must dont proceed unless you have no warning or error messages from UA

    2. If there is warning or error message fix it or make sure how to take care of it after database is restored on SQL server 2016

    3. You might face CE regression on new version of SQl Server 2016. Means query running on 2008 might take more time on 2016. To solve this either tune queries use compatibility level 110 so that old optimizer is used


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, January 12, 2017 4:15 PM
    Moderator