Copying a databse from SQL 2000 to SQL 2005 using Copy Database Wi

Sudhir Darbha

unread,

Nov 16, 2005, 5:03:02 PM11/16/05

to

Hi all,
I am using "Copy Database Wizard" to copy databases from SQL 2k to
SQl2k5. But the package is failing with the following error (event log):-
======================================
Event Name: OnError
Message: ERROR : errorCode=0 description=Invalid column name 'UserRefID'.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: NMSOFFICE\UserName
Source Name: OFFICE2650_OFFICE2650_NMSDATA_Transfer Objects Task
Source ID: {F7955430-FB12-4051-A6DA-323B83480786}
Execution ID: {0306A4C1-9647-4605-AF67-4635234CA5DD}
Start Time: 11/16/2005 3:50:52 PM
End Time: 11/16/2005 3:50:52 PM
Data Code: 0

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
=====================================

But here it doesn't tell me which table this column belongs to.
1. How do i know which table it refers with the information from event log.?
2. Is the database wizard trying to validate each sproc before copying.?
3. Is there any better way of copying databases to sql2005 other than using
copy database wizard?

Thank you,
Sudhir.

Jasper Smith

unread,

Nov 16, 2005, 6:22:48 PM11/16/05

to

I haven't really used the Copy Database Wizard so I can't comment on what
it's trying to do but for question (3) you can just do a backup and restore
or detach/copy/attach of the database files. You need to make sure you add
any required logins to the 2005 server and make sure they map to the users
in your database.

HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546

INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071

INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133

PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001

INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872

PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Sudhir Darbha" <Sudhir...@discussions.microsoft.com> wrote in message
news:E7FBFAF1-8CE3-41A1...@microsoft.com...

Sudhir Darbha

unread,

Nov 17, 2005, 12:12:16 PM11/17/05

to

Smith,
Thank you very much for your reply. I have tried the "Backup and
Restore" method. It restored the database successfully to the 2005 server and
then I updated the logins too. But, when I right-click the database and try
to view "Properties", it throws the following error :-

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property IsPrimaryFile is not available for DataFile '[DBNAME_Data]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=IsPrimaryFile&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

==================

It appears to me that the server is not able to recognize DBNAME_DATA as a
PRIMARY file. Do you have any idea as to whats going on here?
Apart from this i'm able to view the data, sprocs, views, etc.

Thanks,
Sudhir.

Jasper Smith

unread,

Nov 17, 2005, 2:01:49 PM11/17/05

to

Try setting the compatability level to 90 for the restored database and see
if that helps

EXEC sp_dbcmptlevel 'database name', '90'

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Sudhir Darbha" <Sudhir...@discussions.microsoft.com> wrote in message

news:848DFAC6-FB47-4DFA...@microsoft.com...