Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2011-10-10
Medium Priority
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

Daniel Reynolds earned 1000 total points
ID: 36946546
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 40

Assisted Solution

lcohan earned 1000 total points
ID: 36952085
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: http://msdn.microsoft.com/en-us/library/ms345408.aspx

Author Closing Comment

ID: 36987185
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.

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A safe way to clean winsxs folder from your windows server 2008 R2 editions
I’m willing to make a bet that your organization stores sensitive data in your Windows File Servers; files and folders that you really don’t want making it into the wrong hands.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

581 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