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

6267
12
Jump to solution
06-23-2016 11:41 AM
GeorgeClark
New Contributor III

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?

GeodatabaseEnterprise GIS

0 Kudos
1 Solution

Accepted Solutions
GeorgeClark
New Contributor III

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.

View solution in original post

12 Replies
AsrujitSengupta
Regular Contributor III

Did you close the ArcCatalog interface...reopen it and then check?

0 Kudos
GeorgeClark
New Contributor III

Yes, I did.  I also closed everything out, restarted my machine, and rechecked.

0 Kudos
AsrujitSengupta
Regular Contributor III

In SQL Server Management Studio...do you still see the SDE Repository tables inside that database?

0 Kudos
GeorgeClark
New Contributor III

No.  I took the pre-deployment backup before geo-enabling the database, and that is what I restored from (a "regular" SQL Server database).

0 Kudos
GeorgeClark
New Contributor III

Here is some additional info that may or may not be helpful.

We have our ArcGIS (version 10.3) running on one server.  Our SQL Server (V2012) is on a different server.

I am running ArcCatalog (10.3.1) from a laptop running Windows 7 Ultimate SP1.

0 Kudos
AsrujitSengupta
Regular Contributor III

I hope you are not using a Basic License of ArcGIS Desktop. The Enable Geodatabase option will be greyed out in that case

0 Kudos
GeorgeClark
New Contributor III

No.  We are using ArcGIS 10.3.1 for Desktop

License type:  Advanced.

If I create a connection to one of our other "regular" SQL Server databases, the Enable Geodatabase... option text color is black.  When I started this process, the option text color was black also, but it changed to grey, after I ran the Enable Geodatabase process, which made sense.

0 Kudos
AsrujitSengupta
Regular Contributor III

The thing is that if ArcGIS was considering it as an Enterprise geodatabase, you will not even get the option 'Enable Geodatabase' anymore.

The fact that it still is showing the option 'Enable Geodatabase', though its greyed out...indicates that ArcGIS considers it as a simple database.

But the question remains as to why that option is greyed out....

0 Kudos
RexRobichaux2
Occasional Contributor II

Hi George,

What user are you using to connect to the database with in ArcCatalog currently? It sounds like the sde login / user / schema might have been altered or deleted and normally needs to be recreated in SQL Server before the enable geodatabase tool can be ran on the client (ArcMap / Catalog) side of things. The following SQL code was ran (in SQL Server 2014 however, and at ArcGIS version 10.4.1)  to successfully prepare a database to be enabled into a geodatabase using the tool in ArcMap / Catalog. The database being enabled = Testtwo and the filename path should be the directory on the SQL Server server where your MDF (data file) and LDF (log file) reside. The first portion is altering the database properties for configuration, the second portion creates a login, user and schema named "sde" and adds sde to the bd_owner role, and finally the last portion grants the necessary permissions to sde. I hope this is of some assistance to you-

USE [master]

GO

/****** Object:  Database [Testtwo]    Script Date: 6/22/2016 8:06:13 AM ******/

CREATE DATABASE [Testtwo]

CONTAINMENT = NONE

ON  PRIMARY

( NAME = N'Testtwo', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Testtwo.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Testtwo_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Testtwo_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [Testtwo] SET COMPATIBILITY_LEVEL = 120

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Testtwo].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [Testtwo] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [Testtwo] SET ANSI_NULLS OFF

GO

ALTER DATABASE [Testtwo] SET ANSI_PADDING OFF

GO

ALTER DATABASE [Testtwo] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [Testtwo] SET ARITHABORT OFF

GO

ALTER DATABASE [Testtwo] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [Testtwo] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [Testtwo] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [Testtwo] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [Testtwo] SET CURSOR_DEFAULT  GLOBAL

GO

ALTER DATABASE [Testtwo] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [Testtwo] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [Testtwo] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [Testtwo] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [Testtwo] SET  DISABLE_BROKER

GO

ALTER DATABASE [Testtwo] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [Testtwo] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [Testtwo] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [Testtwo] SET ALLOW_SNAPSHOT_ISOLATION ON

GO

ALTER DATABASE [Testtwo] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [Testtwo] SET READ_COMMITTED_SNAPSHOT ON

GO

ALTER DATABASE [Testtwo] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [Testtwo] SET RECOVERY FULL

GO

ALTER DATABASE [Testtwo] SET  MULTI_USER

GO

ALTER DATABASE [Testtwo] SET PAGE_VERIFY CHECKSUM 

GO

ALTER DATABASE [Testtwo] SET DB_CHAINING OFF

GO

ALTER DATABASE [Testtwo] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )

GO

ALTER DATABASE [Testtwo] SET TARGET_RECOVERY_TIME = 0 SECONDS

GO

ALTER DATABASE [Testtwo] SET DELAYED_DURABILITY = DISABLED

GO

ALTER DATABASE [Testtwo] SET  READ_WRITE

GO

USE [Testtwo]

GO

CREATE USER [sde] FOR LOGIN [sde]

GO

USE [Testtwo]

GO

ALTER USER [sde] WITH DEFAULT_SCHEMA=[sde]

GO

USE [Testtwo]

GO

CREATE SCHEMA [sde] AUTHORIZATION [sde]

GO

USE [Testtwo]

GO

ALTER ROLE [db_owner] ADD MEMBER [sde]

GO

grant create function to sde;

grant create procedure to sde;

grant create view to sde;

grant create table to sde;

0 Kudos