March 21, 2007 at 5:16 pm
I am really struggling with a very simple task: copying a user database from a 2000 SP4 dev server to 2005 using the Copy Database Wizard. this database has just one object (a table owned by dbo) and just one user (dev2). This dev2 user is actually a Sql Server login that I created on the server beforehand with the same password that is on the source server.
Actually, my copy is somewhat successful: what happens is that the database copies over very nicely from 2000 to 2005 and I can query off of the one table using the sa account.
The problem is this: I cannot get the dev2 login to work. After I copy the database and log into the server using the dev2 login, the database is not valid. It does not recognize the database I have copied over.
So then I do the standard thing that I do to get this to work: attempt to delete the dev2 login (since it owns no objects) and then put it back in. But 2005 will not let me delete the login: it says that it "owns schema". But it does not own any schema! I checked stored procs, tables, etc.
So what is the right way to do this? I looked and didn't see the sp_help_rev_login procedure either.
Btw, detach and attach did not work any better either.
Any help would be much appreciated. I've never had this much trouble moving a 2M database!
March 21, 2007 at 10:56 pm
1. Move Dev2 login to 2005 server using sp_help_revlogin.
2. Backup the sql 2000 user database and restore on to sql 2005.
Or
Dettach the database manually using sp_dettachdb command and copy the mdf and ldf files and attach the copied files on sql 2005 server using sp_attachdb...See BOL for the syntax...
3. Change the default db of dev2 login.
Note: Copy db wizard also uses detach attach method in the background...
MohammedU
Microsoft SQL Server MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply