Trouble migrating SQL Server 2005 dbs from 32 bit Windows 2003 to 64 bit Windows 2008 server

Posted on 2011-10-10
Last Modified: 2012-05-12
I am trying to help a customer with the following error.  Is there an issue with SQL Server 2005 running on a 64 bit Windows 2008 server?  Or is there a flaw in his approach to migrating the dbs?  If it matters, the apps that access the dbs are MS Great Plains V 10 and Softrax operations 9.0 SP1.

They have applied SQL SP 2, but are waiting for confirmation that Softrax will run on SP 3 before applying.

"I tried using the backup tool in sql and then restoring to the new machine.  This seemed to work great until I got to the master database…it said I had to go into single user mode. This I did and was able to get a backup.

All seemed to restore on the new box. SQL would start will all databases copied over to the new box except the master DB.  When I would copy that DB and try to start the service it would start/stop immediately

So I tried to restore all DB’s except master…I renamed the new box to “SQLServer" like the production box and tried to login with Great Plains. It would not authenticate to the new SQLServer. I can’t remember the message, but it was very similar to what you would get with an incorrect username and/or password.

Then the consultant came in and using SQL Management studio he connected to both SQLServers. Both could be seen in the management studio, but when he would try to run the database copy wizard….it would appear to transfer the data, but then error out near the end.. I did a test run of one of our non essential databases and attached the error.

Failed with the following error: "The procedure 'sys.sp_procoption' cannot be executed within a transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Question by:woodwyn
    LVL 17

    Accepted Solution

    Is there a particular reason you need to copy the master database? I have migrated to different servers and never copy the master database.
    LVL 39

    Assisted Solution

    Please keep in mind that "With the Copy Database Wizard, you cannot copy extended stored procedures, alerts, DTS packages, or linked server configurations. You must move these manually" so I won't use that method either way bu Detach/Copy db files/Attach or the best to Backup Restore except master of course.

    Please see more about movind system db's at:

    Author Closing Comment

    We reinstalled SQL and did not copy the system DBs nor did we use the Wizard.  All is well.  FYI, following the Great Plains move instructions posted on the MS support site was the key.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
    This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now