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​

[AsrujitSengupta]

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

[GeorgeClark]

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

[AsrujitSengupta]

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

[GeorgeClark]

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

[GeorgeClark]

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.

[AsrujitSengupta]

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

[GeorgeClark]

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.

[AsrujitSengupta]

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....

[RexRobichaux2]

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;