Community
Showing results for 
Search instead for 
Do you mean 
Reply

Principal error when restoring database.

Highlighted
New Member
Posts: 17
Country: Canada

Principal error when restoring database.

We're looking at upgrading from Act 20.1 to Act 22.  I think my boss has already purchased the licences, as we wanted to get the discounted pricing available prior to Dec 31, 2019.

 

I'm doing some testing on a trial install of v22, and I'm getting an error when restoring the database:

 

"The database principal owns a schema in the database, and cannot be dropped."

 

There are then a bunch more lines, some of which list SQL users, including ACTUSER, ACTOLEDB, etc.

Then some more that look like .NET exceptions.

 

I've tried this restore on v22, and when it failed, also tried it on a v20.1 machine, and it failed with the same error.  Even the machine which actually made the backup fails in the same way.

I'm using "Restore As.." so it's not that.

 

I've tried using SQL Management Studio, and deleting the SQL users that are mentioned in the error text, and their associated schemas, with no success.

There are a couple of obvious ACT users that are _not_ mentioned in the error, which I didn't delete.

 

After a restore failure, these SQL users are not back in SQL Server, so I'm confused as to how it doesn't seem to be able to drop a user that doesn't exist.

 

I'm going to try deleting _all_ obvious Act users from SQL Server, and report back, but wanted to get this posted to see if anyone had any other suggestions.

 

Attached is the full error message.

Highlighted
New Member
Posts: 17
Country: Canada

Re: Principal error when restoring database.

So, deleting all ACT* users from SQL Server didn't change anything.

I also found a role and schema named ACTRESTORE, that were not associated with the ACTRESTORE user, and deleted those.

 

No success, though, as I'm still getting the same error when restoring.

 

 

Highlighted
Nickel Elite Contributor
Posts: 534
Country: Germany

Re: Principal error when restoring database.

[ Edited ]

2 quick recommendations:

a) try to restore another, older backup for testing purposes.

b) hands off of the v22. Lots of unresolved, new problems which were not present in v21.1.. 21.1. Is the latest fairly stable version.

 

Btw, is there anything "forcing" you to update from v20.1? This version is basically also quite stable....

Andreas Schlesselmann
Melville-Schellmann GbR
Germany
www.melville-schellmann.de
Highlighted
New Member
Posts: 17
Country: Canada

Re: Principal error when restoring database.

We would probably go with 21.1, but we missed the deadline for a 21.1 licence with a 22 licence thrown in for free.

 

As to why we're upgrading at all:

1) We wanted to get to the latest version for support reasons, prior to Act becoming a subscription-only product with their Growth Suite.  We have no plans to move to GS, and a disinclination to do so, but since desktop is coming to an end, we wanted to go as far as we could on this track.

2) We're having some odd problems with Durkin Impact Suite, which may be fixed with a newer version of Act.  Of course, it might be that we have some database issues that are causing it, instead, but this was our thinking prior to discovering this DB restore issue.

 

I'll grab an older backup and see if a restore works on that.

 

Stand by for updates.....

 

Highlighted
New Member
Posts: 17
Country: Canada

Re: Principal error when restoring database.

[ Edited ]

Ok...so, part of the problem may be the setup we have....

 

The Act! server runs on a VM, and the VM is backed up automatically overnight.  I've tested restoring this VM on a number of occasions, and it always works fine.

As a result, we don't have a lot of backups from within Act.  All my recent ones have the same problem.  I have to go back to 2017 in order to find a backup that restores properly.

 

(Edit:  My boss might have some more recent functional ones on his laptop, as he occasionally takes the DB home to do work over holidays, but he's not in the office today to check.)

 

I have successfully imported a SQL .bak file (made with ActDiag) into the new version, and it worked properly, but then when I start Act, it complains about lots of files, layouts, etc missing.

 

If I were to copy the entire "MyActDB-database files" folder into the correct place, and restore the DB from the SQL .bak file, would I have everything I need at this point?  Is there anything else that's included in an Act backup that I wouldn't get if I did it in this way?

Highlighted
Nickel Elite Contributor
Posts: 534
Country: Germany

Re: Principal error when restoring database.

Quote:"If I were to copy the entire "MyActDB-database files" folder into the correct place, and restore the DB from the SQL .bak file, would I have everything I need at this point?  Is there anything else that's included in an Act backup that I wouldn't get if I did it in this way?"

 

No, that's the right way to go.

The only thing that might happen is that the SQL-Log file has the wrong name "MyActDB.MLF" (normally it's named MyActDB.ALF) because of the unusual restore way from a BAK file, but it works even with the "wrong name".

 

Andreas Schlesselmann
Melville-Schellmann GbR
Germany
www.melville-schellmann.de
Highlighted
New Member
Posts: 17
Country: Canada

Re: Principal error when restoring database.

I figured this out. In Act 20, and probably other versions, there are a bunch of SQL users and schemas for various functions. For all of them, the schema name and username are identical.

Somehow, somewhere along the way, one of my schemas had its owner changed to another user, so inevitably, when the restore process tried to delete the user that was now owning this schema, it couldn't be deleted, because the "stolen" schema hadn't been deleted yet.

Returned that schema to its original owner, and the backup and restore process works flawlessly.