Restore 2003 Server Database transferred to an SQL Server 2005

[Stephan Steiner]

Hi

In order to migrate our test project server to 2007 I moved a copy of
the 2003 database (running on sql server 2000) to my 2007 server
(running sql server 2005). I migrated and it worked out just fine.
However, in the meantime, somebody messed up some of the projects on
the 2003 server, so knowing that the the database that is on the 2007
server is okay, I figured let's just detach it, and reattach to the
2003 server.

That wasn't such a great idea as now I'm stuck with a DB that I cannot
attach on sql server 2000. Since I absolutely need that data back and
there are no backups (yeah I know.. ordered 4 TB of backup space last
week but it has yet to get to me) I used the hard way to go back..
extract the schema from the 2005 server in 2000 style, loaded it on
the 2000 server, then exported the data (had to do it in many steps as
copying a lot of table makes the management tool go nuts and try to re-
create tables even though I said not to), then finally restored
triggers and FKs (turns out there's but one trigger and no FKs). Then
I re-setup the two MSProject* users as usual and figured that's it.

But no.. I still got the 5001 error upon trying to log in. After a
while of scratching my head I looked at the roles more closely and
realized that even though they were there, the securables were blank.
So.. I restored those as well and sure enough no more error 5001 upon
login. However, now I'm stuck with a 5016 error.. no matter what
account I try (both domain and project server accounts) project server
2003 tells me that the account has no permissions to login in.

So, something is still b0rked but I cannot figure out what it could
be. Any ideas?

I already considered loading a backup from the productive server which
would obviously work, but the test server contains certain projects I
need and afaik restorep needs the project to be in both databases.

I'm at the end of my rope here and would really appreciate some ideas
on how to get my test server back to working status.

Regards
Stephan

P.S. I considered that I might be able to simply use the database on
the 2007 server but I'd like to remain with one machine for the entire
functionality.. I don't know yet what we'll do with the 2007 server.

 

[Gary L. Chefetz]

Stephan:

First of all, what do you mean by "2007 Server?" Are you talking about
Project Server 2007? If so, there's no way that I know of to downgrade a
2007 database to 2003. Certainly the mechanations you're going through seem
rather futile. If you have the projects in a 2007 database and they're OK,
why not save the project files as 2003 mpp files using the Project client?

I may be totally misunderstanding what you're after here. Inasmuch as nobody
else has replied, I assume others are equally confused.

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting: http://www.msprojectexperts.com
Project Server Training: http://www.projectservertraining.com
Project Server FAQS: http://www.projectserverexperts.com
Project Server Help Blog: http://www.projectserverhelp.com

 

[Stephan Steiner]

Gary

2007 Server = Project Server 2007. 2003 Server = Project Server 2003
and similarly 2000 server = SQL Server 2000 (= the SQL server for the
project server 2000) and 2005 server = SQL Server 2005 (= the SQL
server for project server 2007).

Basically it's a problem on how you downgrade a Database from an SQL
Server 2005 to an SQL Server 2000 (I attached a perfectly working
ProjectServer database from my Project Server 2003 box to my Project
Server 2007 box because in order to upgrade both DBs should be on the
same SQL Server.. and attaching a database detached from an SQL Server
2000 to an SQL Server 2005 upgrades that database to 2005 level and
thus the database cannot be re-attached to an SQL Server 2000). I
managed to go through that process and have all my data back in a
working SQL Server 2000 database, but Project Server 2003 refuses to
let me log in.. so I figure somewhere during my downgrade process I
lost something but I don't know what. So, I'm now trying to downgrade
a Project Server 2007 database to Project Server 2003 (I know that
will never work), but take a Project Server 2003 database, detach it,
attach it to an SQL Server 2005, then detach it and re-attach it to an
SQL Server 2000.

So my questions is how I can figure out what causes the 5016 error and
how to fix it. It seems Project Server 2003 uses the appropriate
account and has the appropriate execute permissions, but then the
authorization process still fails even for non domain accounts. I also
verified that the MSP_RESOURCES and MSP_WEB_RESOURCES contain the
appropriate data.

Regards
Stephan