How to delete SDE database that was already dropped in SQL Server

[GeorgeClark]

I am relatively new to the ArcGIS world but am experienced with SQL Server.  We are geo-enabling a database that already exists in SQL Server.  In preparation for deploying to Test and later to Production, I am writing a how to guide, to make all the steps consistently reproducible.  Before starting, I took a backup of the database.  After geo-enabling the database, I missed documenting a step in ArcCatalog.  Without thinking, I quickly dropped the database in SQL Server, and restored it from the backup, to simply start over.  Now, although the restored non-geo-enabled database is what I have in SQL Server, ArcCatalog still sees the database as geo-enabled.  In ArcCatalog, when I right-click on the database connection, the Enable Geodatabase option is disabled.

Corrective action so far.

Delete the connection in ArcCatalog and recreate it:  No help.

Delete the connection in ArcCatalog, then drop the database in SQL Server, then restore the database with the same name, then recreate the db connection:  No help.

Basically is seems that ArcCatalog now/still recognizes the database as a geo-enabled and registered database.  I do not want to change the name of the database.

How can I clean this one database out and start over?

Geodatabase​ Enterprise GIS​

[RexRobichaux2]

Alternatively, if the sde login still exists, you could also first try just remapping the sde user (must be done after SQL Server database restore) using the following script in SQL Server Management Studio where db_name = the database name being restored:

use db_name

                  go

                  EXEC sp_change_users_login 'Update_One', 'sde', 'sde'

                  go

For more: Enable Enterprise Geodatabase—Help | ArcGIS for Desktop

[GeorgeClark · Accepted solution]

Firstly, thanks to Asrujit and Rex for your assistance, I appreciate it.

For a reason unrelated to this issue, at the end of the day, we restarted our ArcGIS server.  We added
RAM and had to restart.

After restarting, Tina M. (ESRI Tech) called.  We opened ArcCatalog again, to see review the situation.  This time the Enable Geodatabase text color was black again, without us doing anything.  Never the less at my request, we went through the process of dropping the database, and bringing it back in, and things worked OK.  It seems that something was amiss with the server, and when my folks restarted it, the problem cleared.  For the benefit of anyone else who finds this discussion, I will summarize and include some info, for other “newbees” like me.

Before starting, note that we cannot use a Basic License to do this task (from Asrujit).

(from Tina)

Steps to create the geodatabase.

1. Create your SQL Server database in SQL Server
2. Create your database connection in ArcCatalog
3. Enable the database as a geodatabase using ArcCatalog
   - Open the database connection
   - In the Catalog Tree, right-click on the database connection and select Enable Geodatabase
4. Register the database connection in ArcGIS for Server

Steps to delete the geodatabase (the official way) -- How to clean out a database and start over.

1. In ArcCatalog remove the connection from the data store
2. In ArcCatalog, remove the database connection
3. In SQL Server, drop the database

In my case today, what we did was…

- In SQL Server, just drop the table again

- In SQL Server, restore it from the same backup as I did before, so it had the same name as I wanted

- In ArcCatalog, since I still had the database connection, right-clicked on Enable Geodatabase

- We checked the database properties, and the table was already registered

Conclusion:  What I was trying to do earlier should have worked, but something was amiss and restarting our ArcGIS server cleared that problem.  After the restart, everything worked properly.

[RexRobichaux2]

Hi George,

Glad to hear you were able to resolve the issue and are up and running!