ArcGIS Help 10.2 - Scripting database and geodatabase creation in SQL Server

You can copy and alter one of the example scripts provided. One creates both a database and geodatabase in a Microsoft SQL Server instance. The other creates a geodatabase in an existing, preconfigured SQL Server database.

There are additional tasks you must complete before running the script. Those tasks and the script itself are described in the following steps:

Steps:

Install and configure SQL Server on your server.
Be sure the SQL Server instance can accept connections (for example, that the services are running and a communication protocol is enabled).
If you will be running the Python script from a different computer than the server where SQL Server is installed, install a compatible SQL Server Native Client on the remote computer.
The SQL Native Client can be downloaded from the Esri Customer Care Portal. See Setting up a connection to SQL Server for more information.

Create a text file on the machine where ArcGIS for Server, ArcGIS for Desktop (Standard or Advanced), or ArcGIS Engine (with the Geodatabase Update extension) is installed and copy one of these example scripts into the text file:

This example creates a database, sde login and schema (if using an sde schema), grants the sde user privileges to create a geodatabase, creates a database, then creates the geodatabase.

"""
Name: create_enterprise_gdb.py
Description: Provide connection information to a DBMS instance and create an enterprise geodatabase.
Type  create_enterprise_gdb.py -h or create_enterprise_gdb.py --help for usage
Author: Esri
"""

# Import system modules
import arcpy, os, optparse, sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")                   
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("--auth", dest="Account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-U", dest="Dbms_admin", type="string", default="", help="DBMS administrator user")
parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
parser.add_option ("--schema", dest="Schema_type", type="choice", choices=['SDE_SCHEMA', 'DBO_SCHEMA'], default="SDE_SCHEMA", help="Schema Type for SQL Server geodatabase, SDE or DBO. Default=SDE_SCHEMA")
parser.add_option ("-u", dest="Gdb_admin", type="string", default="", help="Geodatabase administrator user name")
parser.add_option ("-p", dest="Gdb_admin_pwd", type="string", default="", help="Geodatabase administrator password")
parser.add_option ("-t", dest="Tablespace", type="string", default="", help="Tablespace name")
parser.add_option ("-l", dest="Authorization_file", type="string", default="", help="Full path and name of authorization file")
# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	
	#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
		parser.print_help()
		sys.exit(3)

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	database = options.Database.lower()	
	account_authentication = options.Account_authentication.upper()
	dbms_admin = options.Dbms_admin
	dbms_admin_pwd = options.Dbms_admin_pwd
	schema_type = options.Schema_type.upper()
	gdb_admin = options.Gdb_admin
	gdb_admin_pwd = options.Gdb_admin_pwd	
	tablespace = options.Tablespace
	license = options.Authorization_file
	
	
	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
	
	if( database_type ==""):	
		print " \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified.")
		parser.print_help()
		sys.exit(3)		
		
	if (license == ""):
		print " \n%s: error: \n%s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
		parser.print_help()
		sys.exit(3)			
	
	if(database_type == "SQL_SERVER"):
		if(schema_type == "SDE_SCHEMA" and gdb_admin.lower() != "sde"):
			print "\n%s: error: %s\n" % (sys.argv[0], "To create SDE schema on SQL Server, geodatabase administrator must be SDE.")
			sys.exit(3)
		if (schema_type == "DBO_SCHEMA" and gdb_admin != ""):
			print "\nWarning: %s\n" % ("Ignoring geodatabase administrator specified when creating DBO schema...")
		if( account_authentication == "DATABASE_AUTH" and dbms_admin == ""):
			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified with database authentication")
			sys.exit(3)
		if( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != ""):
			print "\nWarning: %s\n" % ("Ignoring DBMS administrator specified when using operating system authentication...")	
	else:
		if (schema_type == "DBO_SCHEMA"):
			print "\nWarning: %s %s, %s\n" % ("Only SDE schema is supported on", database_type, "switching to SDE schema..." )
			
		if( gdb_admin.lower() == ""):
			print "\n%s: error: %s\n" % (sys.argv[0], "Geodatabase administrator must be specified.")
			sys.exit(3)

		if( gdb_admin.lower() != "sde"):
			if (database_type == "ORACLE"):
				print "\nGeodatabase admin user is not SDE, creating user schema geodatabase on Oracle...\n"
			else:
				print "\n%s: error: %s for %s.\n" % (sys.argv[0], "Geodatabase administrator must be SDE", database_type)
				sys.exit(3)
			
		if( dbms_admin == ""):
			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!")
			sys.exit(3)

		if (account_authentication == "OPERATING_SYSTEM_AUTH"):
			print "Warning: %s %s, %s\n" % ("Only database authentication is supported on", database_type, "switching to database authentication..." )

	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print "\n" + product_license + " license found!" + " Creating an enterprise geodatabase requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
		sys.exit("Re-authorize ArcGIS before creating enterprise geodatabase.")
	else:
		print "\n" + product_license + " license available!  Continuing to create..."
		arcpy.AddMessage("+++++++++")
	
	
	try:
		print "Creating enterprise geodatabase...\n"
		arcpy.CreateEnterpriseGeodatabase_management(database_platform=database_type,instance_name=instance, database_name=database, account_authentication=account_authentication, database_admin=dbms_admin, database_admin_password=dbms_admin_pwd, sde_schema=schema_type, gdb_admin_name=gdb_admin, gdb_admin_password=gdb_admin_pwd, tablespace_name=tablespace, authorization_file=license)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print "\n"
		parser.print_help()   
		parser.exit(2)

If you prefer to preconfigure a SQL Server database, for example, you want to specify a different file size or location, copy this example script to enable geodatabase functionality in an existing, preconfigured SQL Server database.

"""
Name: enable_enterprise_gdb.py
Description: Provide connection information to an enterprise database
and enable enterprise geodatabase.
Type enable_enterprise_gdb.py -h or enable_enterprise_gdb.py --help for usage
Author: Esri
"""

# Import system modules
import arcpy, os, optparse, sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, POSTGRESQL, DB2, INFORMIX, or DB2ZOS.")
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-u", dest="User", type="string", default="", help="Geodatabase administrator user name")
parser.add_option ("-p", dest="Password", type="string", default="", help="Geodatabase  administrator password")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("-l", dest="Authorization_file", type="string", default="", help="Full path and name of authorization file")


# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	
#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
		parser.print_help()
		sys.exit(3)
	

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	account_authentication = options.account_authentication.upper()
	username = options.User.lower() 
	password = options.Password	
	database = options.Database.lower()
	license = options.Authorization_file


	if( database_type ==""):	
		print " \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified.")
		parser.print_help()
		sys.exit(3)		
		
	if (license == ""):
		print " \n%s: error: \n%s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
		parser.print_help()
		sys.exit(3)

	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
		
	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	if (license == ""):
		print " \n%s: error: %s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
		parser.print_help()
		sys.exit(3)
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print "\n" + product_license + " license found!" + "  Enabling enterprise geodatabase functionality requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
		sys.exit("Re-authorize ArcGIS before enabling an enterprise geodatabase.")
	else:
		print "\n" + product_license + " license available!  Continuing to enable..."
		arcpy.AddMessage("+++++++++")
	
	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database + "_" + username    
	
	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  
	

	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
	
	print "\nCreating Database Connection File...\n"	
	# Process: Create Database Connection File...
	# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
	arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=username, password=password, save_user_pass="TRUE")
        for i in range(arcpy.GetMessageCount()):
		if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
			arcpy.AddReturnMessage(i)
			arcpy.AddMessage("\n+++++++++")
			arcpy.AddMessage("Exiting!!")
			arcpy.AddMessage("+++++++++\n")
			sys.exit(3)            
		else:
			arcpy.AddReturnMessage(i)
			arcpy.AddMessage("+++++++++\n")
	
	
	# Process: Enable geodatabase...
	try:
		print "Enabling Enterprise Geodatabase...\n"
		arcpy.EnableEnterpriseGeodatabase_management(input_database=Connection_File_Name_full_path, authorization_file=license)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print "\n"
		parser.print_help() 
		parser.exit(2)

Save the file with a .py extension.
Run the script, providing options and information specific to your site.

A log file for the geodatabase creation (GDBCreateGeodatabase<#>.log) is written to the directory specified for your %TEMP% variable on the computer where the script is run.

Scripting database and geodatabase creation in SQL Server

Related Topics