locked
move SQL database for server migration RRS feed

  • Question

  • I'm doing a migration from SBS 2003 to SBS 2011 but I am not sure how to move my exisitng SQL database to another server. It's not for sharepoint but for another picece of software. Is there a guide someone can psot a link to or maybe give me some steps? The source server is running SQL server 2005
    Tuesday, May 10, 2011 4:54 PM

Answers

  • Other alternatives are detach and reattaching (using SQL Server Management Studio or sp_detach_db and CREATE DATABASE for ATTACH), or using the copy database wizard in SQL Server Management Studio. The wizard is probably the easiest if you are not an experienced SQL Server user. With Management Studio, connect to the source SQL Server. In Object Explorer, right-click the database, point to Tasks, and then click Copy Database.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by Stephanie Lv Wednesday, May 18, 2011 11:37 AM
    Tuesday, May 10, 2011 5:21 PM
    Answerer
  • 1 Detach and attach them on new server (steps http://support.microsoft.com/kb/224071)

    2. Take backup on primary server and restore them on secondary server

    3. Use the Import and Export Data Wizard to copy objects and data between SQL Server databases

    for steps 2 and 3 please check here http://support.microsoft.com/kb/314546

     

     


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Stephanie Lv Wednesday, May 18, 2011 11:37 AM
    Tuesday, May 10, 2011 7:27 PM
  • I have an article (which is still a bit of work in progress) on this topic: http://www.karaszi.com/SQLServer/info_moving_database.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Stephanie Lv Wednesday, May 18, 2011 11:37 AM
    Wednesday, May 11, 2011 6:52 AM

All replies

  • The easiest would be to:

    On source server:

    backup database [yourDBName] to disk = 'C:\tmp\mybackup.bak'

    On destination server:

    copy bak frile from above step to temp folder

    restore datanase [yourDbName] from disk = 'c:\temp\mybackup.bak'


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Tuesday, May 10, 2011 5:04 PM
  • Other alternatives are detach and reattaching (using SQL Server Management Studio or sp_detach_db and CREATE DATABASE for ATTACH), or using the copy database wizard in SQL Server Management Studio. The wizard is probably the easiest if you are not an experienced SQL Server user. With Management Studio, connect to the source SQL Server. In Object Explorer, right-click the database, point to Tasks, and then click Copy Database.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by Stephanie Lv Wednesday, May 18, 2011 11:37 AM
    Tuesday, May 10, 2011 5:21 PM
    Answerer
  • You mean using DTSWizard.exe?
    Tuesday, May 10, 2011 5:35 PM
  • 1 Detach and attach them on new server (steps http://support.microsoft.com/kb/224071)

    2. Take backup on primary server and restore them on secondary server

    3. Use the Import and Export Data Wizard to copy objects and data between SQL Server databases

    for steps 2 and 3 please check here http://support.microsoft.com/kb/314546

     

     


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Stephanie Lv Wednesday, May 18, 2011 11:37 AM
    Tuesday, May 10, 2011 7:27 PM
  • I have an article (which is still a bit of work in progress) on this topic: http://www.karaszi.com/SQLServer/info_moving_database.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Stephanie Lv Wednesday, May 18, 2011 11:37 AM
    Wednesday, May 11, 2011 6:52 AM