Trying to Copy a user database from one SQL server to another

[dheim70]

Hi folks,

Trying to copy a user database from an SQL Server 2005 to an SQL Server 2008 install on another server. I am using the copy wizard to do this. I have the same Domain account setup on both SQL servers and both have sysadmin rights at the server level. However when I run the job at the end of the wizard I am getting the following error stack:

Message=Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "EXEC sp_procoption N'[dbo].[ASPState_Startup]', 's..." 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.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferExtraObjectsViaSmoTransfer()
Operator: XXXXXX\XXXXXXXXX
Source Name: XXXXXXXXXXXXXXXXX Source ID: {F0196701-F147-4191-BA0A-8F3FBDE09967}
Execution ID: {8B9AB025-69E1-46D4-A04D-9CE7CBCA9E84}
Start Time: 9/23/2011 11:53:17 AM
End Time: 9/23/2011 11:53:17 AM
Data Code: 0

Have no clue how to decipher this? Can anyone please help?

 

[simian336]

Much easier just to take a backup then restore it to the new machine.

Simi

 

[dheim70]

I thought about that as well, but I also wanted to be able to move over all the logins and stored procedures as well.

 

[gmmastros]

Backup and restore will move the stored procedures. I mean... the stored procedures are part of the database so backup and restore will include them.

As far as the logins... it's actually not too difficult to move them also. Read the following article from Microsoft.

http://support.microsoft.com/kb/246133

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dheim70]

Thanks, I'll check that out and work up a procedure as I am going to have to make this move for about 15 databases

 

[SQLBill]

I suggest you run the upgrade wizard to see if there is anything in the SS2005 database(s) that is not compatible with SS2008.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875