Error copying SQL Server 2000 database to SQL Server 2008

Question

I'm trying to upgrade a database from a SQL Server 2000 instance to a SQL Server 2008 instance. I'm doing this by right clicking on the database and selecting copy database. My current issue is that I'm getting this error in the log file:

OnError,AQUE-SQLDEV,NT AUTHORITY\SYSTEM,aque-db2000_aque-sqldev_sql2008_Transfer Objects Task,{F0ACDE4D-D023-400C-BE3C-91CD3A537988},{40E67169-0F3F-4F86-AD2E-6E2CB532BA5C},18/10/2011 15:38:37,18/10/2011 15:38:37,0,0x,Script failed for User 'WebUser'. StackTrace: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Transfer.Microsoft.SqlServer.Management.Common.ITransferMetadataProvider.SaveMetadata() at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.Configure(ITransferMetadataProvider metadataProvider) at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer() InnerException-->Creating a user without an associated login is not supported in SQL Server 2008.;

Does anyone know why this might be happening?

Thanks,

Sachin

"Does anyone know why this might be happening?" ->>> 'Creating a user without an associated login is not supported in SQL Server 2008' — Mitch Wheat
– Mitch Wheat, Commented Oct 18, 2011 at 14:52
I did know that but how do I prevent the wizard from creating such users? — Sachin Kainth
– Sachin Kainth, Commented Oct 18, 2011 at 15:00
Don't use Copy database - create a backup on SQL Server 2000 and restore on SQL Server 2008. — marc_s
– marc_s, Commented Oct 18, 2011 at 18:41

Mike Thien · Accepted Answer · 2011-11-17 16:35:23Z

1

Based on the error it sounds like you need to create a login first on the SQL 2008 server that matches the same login/user that exists on the SQL 2000 server. You could do a couple of things:

create a new database in 2008 to migrate to
create a new login in 2008 that matches the existing login from sql 2000
map the new login to that new database from step #1
run the copy database wizard

If it was me, I would do as marc_s suggested and perform a full backup of the existing database on sql 2000, then restore to a blank database in sql 2008. If you can, update the compatibility mode in database properties to be 2008. I've done this hundreds of times and works like a charm.

answered Nov 17, 2011 at 16:35

Mike Thien

3301 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Salman Arshad Over a year ago

In my case it was an "orphan login". The source database had a SQL login listed under Databases/<dbname>/security/users but there was no corresponding login in Databases/security/logins -- I deleted the orphan and the wizard was happy.

Calciol · Accepted Answer · 2012-01-03 07:12:36Z

1

The reason can be that a file with the new Database name already exist on the filesystem. We encountered this when we renamed Database X to X_Old, and tried to copy database Y to X. This cannot be done, because database X_Old is still associated with the filename X.

Either delete the conflicting database, or rename the file on the file system.

See http://codecopy.wordpress.com/2012/01/03/error-while-copying-a-database/

answered Jan 3, 2012 at 7:12

Calciol

4494 silver badges6 bronze badges

Collectives™ on Stack Overflow

Error copying SQL Server 2000 database to SQL Server 2008

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related