CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2000
Sachin Kainth
Sachin Kainth

Reputation: 46740

Error copying SQL Server 2000 database to SQL Server 2008

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

Upvotes: 2

Views: 1189

Answers (2)

Calciol
Calciol

Reputation: 449

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/

Upvotes: 1

Mike Thien
Mike Thien

Reputation: 330

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:

  1. create a new database in 2008 to migrate to
  2. create a new login in 2008 that matches the existing login from sql 2000
  3. map the new login to that new database from step #1
  4. 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.

Upvotes: 1

Related Questions