topic Re: Can not connect to SDE when restored from backup in Data Management Questions

Can not connect to SDE when restored from backup

wentinghan — Sun, 12 Dec 2021 07:24:03 GMT

Hi, All:

We are tring to restore a sde on db2 from a backup, We use the flowing command to do the restore:

    db2--> restore db SDEDB from /db2/sde5 taken at 20150919163011……

The restore was successful, we check the table number, index number, they are the same as the old database. Here are the software we are using:

   Database: DB2 9.7.2
   Operating System: AIX 6.1
   SDE: 10.0
   ArcGIS Desktop: 10.0 SP5

When I try to make a direct connection to DB2 from ArcCatlog, it gave me an error message states:

“This release of the GeoDatagbase is either invalid or out of date. DBMS table not found [SDEDB.SDE.GDB_Release]”;

It is quite strange that “SDE.GDB_Release” is not exist in the source gdb either, but my ArcCatlog works fine with the source DB2.

Then I try to check out the privileges of the sde user, so I grant the flowing privileges to the SDE, although it may already have these privileges:

CONNECT to database;
CREATETAB in database;
CREATEIN, ALTERIN, and DROPIN for the necessary schema;
EXECUTE on MON_GET_CONNECTION;



CONTROL on database objects, SELECT on SYSIBM.SYSDUMMY1
SELECT on SYSCAT.ROLEAUTH, SYSCAT.DBAUTH, and SYSCAT.TABAUTH

DBADM authority
SYSCTRL or SYSADM authority
EXECUTE on MON_GET_CONNECTION

Unfortunately, the error remains, I still can NOT make a direct connection to the new DB2.

I already search the google, most of the documents point out it is compatibility between the geodatabase and the ArcGIS Desktop, but we have a 10.0 gdb and 10.0 sp5 desktop.

Any help is appreciate, we are quite desperate, thank you very much.

Re: Can not connect to SDE when restored from backup

George_Thompson — Thu, 24 Sep 2015 12:06:48 GMT

Hi Wenting,

Did you happen to rename the database during the restore (i.e. was called "database" and is now "databasenew")?

It looks like that might have been the case with the first DB2 command in the post.

ArcGIS Desktop

-George

Geodatabase Enterprise GIS

Re: Can not connect to SDE when restored from backup

wentinghan — Thu, 24 Sep 2015 12:11:21 GMT

Hi, George:

Yes, I did that. I have changed the database name. Is this a possible reason for that error?

I will try to restore the db with the same name to see if it will work, will post the result here, thank you very much.

Re: Can not connect to SDE when restored from backup

George_Thompson — Thu, 24 Sep 2015 12:17:55 GMT

Hi Wenting,

I think that is the reason for the error. When the database is created there are some tables/triggers/procedures that get hard coded in the geodatabase. If the name changes, then these items break.

I think that if you restore the DB2 database to the new server with the same name, you should be able to connect after that.

-George

Re: Can not connect to SDE when restored from backup

wentinghan — Thu, 24 Sep 2015 12:29:27 GMT

Hi, George:

Thank you very much for your explaination.

I need several hours to restore the database again, I will post the result here so it can help others.

Re: Can not connect to SDE when restored from backup

RebeccaStrauch__GISP — Thu, 24 Sep 2015 13:43:22 GMT

Check out Vince's instruction for move an SDE to a new server, pretty straightforward steps.

Re: Migrate from OLD SDE Server to New One

Re: Can not connect to SDE when restored from backup

wentinghan — Fri, 25 Sep 2015 09:16:24 GMT

Hi,George:

We use the same database name when we do the restore, It succeed!

Thank you very much for your advice, help this post will help others.

Re: Can not connect to SDE when restored from backup

wentinghan — Fri, 25 Sep 2015 09:18:37 GMT

Thanks, Rebecca.

The URL you provided talks about ArcSDE On SQL Server. The process is a little different from DB2.

Re: Can not connect to SDE when restored from backup

JamieLeitch1 — Wed, 19 Dec 2018 19:24:21 GMT

I've encountered this same error when trying to connect to an enterprise geodatabase in SQL Server 2017 that had been restored from a backup and renamed using SQL Management Studio. The problem is that there are several tables and stored procedures that contain the name of the original database that the backup was made from. To connect to this new renamed restored database, you need to update these tables and stored procedures with the new database name.

Check these tables:

GDB_ITEMS

SDE_column_registry

SDE_geometry_columns

SDE_layers

SDE_process_information

SDE_table_registry

And these stored procedures:

create_version

delete_version

edit_version

You should see the name of the original database within those objects. Best test what happens in a test db first if you do the find/replace.

Re: Can not connect to SDE when restored from backup

George_Thompson — Thu, 20 Dec 2018 12:41:58 GMT

Jamie Leitch‌ you are correct, for SQL Server you cannot rename the DB when restoring. Here is some documentation that may be useful; Methods to move a geodatabase in SQL Server—Help | ArcGIS Desktop Geodatabase‌

No matter which of the methods you use to move your SQL Server database, you cannot rename the database. When you restore a database, for example, you are given the opportunity to restore it with a different name. Don't do this with a geodatabase; you won't be able to connect to it.

Re: Can not connect to SDE when restored from backup

JamieLeitch1 — Thu, 20 Dec 2018 13:47:39 GMT

Actually, on a test enterprise geodatabase I was able to essentially "find and replace" any occurrence of the old database name with the new database name, and I can now connect to this new database using ArcGIS Desktop. I haven't done extensive testing, but I am able to edit feature classes, and publish services to Server. If my testing continues to be successful, I will be able to create a SQL Server Agent to perform the backup, restore, and alterations to tables and stored procedures and run on a schedule.

I created a SQL stored procedure that showed me all of the objects in the database that contained a record with the old database name in it. I could then run queries to update these tables with the new database name, and also alter the stored procedures with the new database name.

Re: Can not connect to SDE when restored from backup

George_Thompson — Thu, 20 Dec 2018 14:22:51 GMT

Interesting to see it worked, but would not recommend it. There may be other references in the GDB to the "old" name for certain functions.

Re: Can not connect to SDE when restored from backup

JamieLeitch1 — Thu, 20 Dec 2018 14:33:36 GMT

This is true. However, I've searched/replaced the definitions of every trigger, stored procedure, and function, and the records of every table to find occurrences of the old name. This is not to say that it won't fail in the future as configuration changes are made to the original gdb. Further testing and time will tell...