locked
Move SQL bases RRS feed

  • Question

  • Hi,

    We have some physical servers today that is running SQL server 2008 R2, and we going to migrate these to SQL server 2014 running on VMware. We are going to perform a clean install of the machines on VMware, and want to just detach and attach the database on the new servers, or do a kind of backup and restore on the new.

    Im not to familiar with SQL, but have done some detach and attach before from 2005-2008, so could someone please provide me with some guides. Either backup and restore or detach and attach.... or if we should do it another secure way ?

    There is no problem with downtime.


    /Regards Andreas

    Monday, April 27, 2015 8:31 AM

Answers

All replies

  • Both works, but personally I feel Backup and Restore is certainly a much graceful way to do. I would prefer going with backup/restore instead of attach/detach.

    This would help with upgrade.

    UPGRADING TO SQL SERVER 2014: A DOZEN THINGS TO CHECK

    http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/

    • Edited by dave_gona Monday, April 27, 2015 9:12 AM
    • Marked as answer by Andreas2012 Monday, April 27, 2015 11:03 AM
    Monday, April 27, 2015 9:04 AM
  • Hi,

    Ok so if I do the following it will be ok...

    Right click the running database, and select Backup->Backup Type Full, Select a destination, and click Backup. Then om the new server I right click databases and select Restore Database and point to the filname.bak ?

    Do I also need to move these master,model,msdb,tempdb from the old server ?

    Thanks for reply.


    /Regards Andreas

    Monday, April 27, 2015 9:38 AM
  • Hi,

    Ok so if I do the following it will be ok...

    Right click the running database, and select Backup->Backup Type Full, Select a destination, and click Backup. Then om the new server I right click databases and select Restore Database and point to the filname.bak ?

    Do I also need to move these master,model,msdb,tempdb from the old server ?

    Thanks for reply.


    /Regards Andreas

    since it is new install of sql 2014..  you cannot use old system databases(sql 2008r2) on it... you will already have the system database(master,model,tempdb,msdb) on the new sql server

    you need make sure you migrated all the logins and sql agent jobs/operators/alerts etc..


    Hope it Helps!!

    Monday, April 27, 2015 10:21 AM
  • Hi,

    Ok so I dont move the system databases, but there should not be a problem to restore a database from 2008R2 on the 2014 I guess.

    When it comes to SQL agents jobs/operations/alerts, I dont think this is used at all, how can I see if any is configured ?


    /Regards Andreas

    Monday, April 27, 2015 10:30 AM
  • yes, you can always restore a user database from lower version to atleast similar version of higher version.

    sql 2008r2 is lower version  compared  to sql 2014.

    you should start the sql server agent- it is disabled now..and then expand the node..(it  is very last node in the pic above)

    and expand the nodes inside the sql server agent to find jobs/operators/alerts...

    what edition of  sql is this..if this express edition do not worry about sql server agent...


    Hope it Helps!!

    Monday, April 27, 2015 10:35 AM
  • Thanks for fast reply.

    There was 2 jobs under the "Jobs" folder, so then my questions is how do I copy these jobs over to the new server.

    When it comes to edition, its SQL Server 2008 R2


    /Regards Andreas

    Monday, April 27, 2015 10:46 AM