topic Re: How to delete SDE database that was already dropped in SQL Server in Data Management Questions

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

GeorgeClark — Thu, 23 Jun 2016 18:41:07 GMT

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

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

AsrujitSengupta — Thu, 23 Jun 2016 19:16:34 GMT

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

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

GeorgeClark — Thu, 23 Jun 2016 19:18:56 GMT

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

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

AsrujitSengupta — Thu, 23 Jun 2016 20:07:10 GMT

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

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

GeorgeClark — Thu, 23 Jun 2016 20:49:46 GMT

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

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

GeorgeClark — Thu, 23 Jun 2016 20:54:33 GMT

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.

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

AsrujitSengupta — Thu, 23 Jun 2016 21:16:14 GMT

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

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

GeorgeClark — Thu, 23 Jun 2016 21:25:01 GMT

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.

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

AsrujitSengupta — Thu, 23 Jun 2016 23:05:54 GMT

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

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

RexRobichaux2 — Thu, 23 Jun 2016 23:38:53 GMT

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]

/****** 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%)

ALTER DATABASE [Testtwo] SET COMPATIBILITY_LEVEL = 120

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

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

end

ALTER DATABASE [Testtwo] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [Testtwo] SET ANSI_NULLS OFF

ALTER DATABASE [Testtwo] SET ANSI_PADDING OFF

ALTER DATABASE [Testtwo] SET ANSI_WARNINGS OFF

ALTER DATABASE [Testtwo] SET ARITHABORT OFF

ALTER DATABASE [Testtwo] SET AUTO_CLOSE OFF

ALTER DATABASE [Testtwo] SET AUTO_SHRINK OFF

ALTER DATABASE [Testtwo] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [Testtwo] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [Testtwo] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [Testtwo] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [Testtwo] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [Testtwo] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [Testtwo] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [Testtwo] SET DISABLE_BROKER

ALTER DATABASE [Testtwo] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

ALTER DATABASE [Testtwo] SET DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [Testtwo] SET TRUSTWORTHY OFF

ALTER DATABASE [Testtwo] SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [Testtwo] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [Testtwo] SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE [Testtwo] SET HONOR_BROKER_PRIORITY OFF

ALTER DATABASE [Testtwo] SET RECOVERY FULL

ALTER DATABASE [Testtwo] SET MULTI_USER

ALTER DATABASE [Testtwo] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [Testtwo] SET DB_CHAINING OFF

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

ALTER DATABASE [Testtwo] SET TARGET_RECOVERY_TIME = 0 SECONDS

ALTER DATABASE [Testtwo] SET DELAYED_DURABILITY = DISABLED

ALTER DATABASE [Testtwo] SET READ_WRITE

USE [Testtwo]

CREATE USER [sde] FOR LOGIN [sde]

USE [Testtwo]

ALTER USER [sde] WITH DEFAULT_SCHEMA=[sde]

USE [Testtwo]

CREATE SCHEMA [sde] AUTHORIZATION [sde]

USE [Testtwo]

ALTER ROLE [db_owner] ADD MEMBER [sde]

grant create function to sde;

grant create procedure to sde;

grant create view to sde;

grant create table to sde;

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

RexRobichaux2 — Thu, 23 Jun 2016 23:42:25 GMT

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

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

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

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

GeorgeClark — Fri, 24 Jun 2016 15:57:35 GMT

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.

Create your SQL Server database in SQL Server
Create your database connection in ArcCatalog
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
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.

In ArcCatalog remove the connection from the data store
In ArcCatalog, remove the database connection
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.

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

RexRobichaux2 — Fri, 24 Jun 2016 16:06:49 GMT

Hi George,

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