Backing up databases

[TonyAlmeida]

I have been working on the following code, i am trying to back up a gdb. I am first trying it with a file geodatabase then i will eventually like it to work with a SQL Server Express database server.

My following code gives me an error but it does copy all the feature classes. I don't understand why this giving me an error but still creates the feature classes.

Any help would be great.
Thanks.

import arcpy, os, string  arcpy.env.overwriteOutput = True DS3 = "C:\GIS\DS.gdb"  if arcpy.Exists(DS3):     arcpy.Delete_management(DS3)  def CopyDatasets(start_db,end_db,num):         #Set workspaces     arcpy.env.workspace = start_db     wk2 = end_db     datasetList = arcpy.ListDatasets()      #for feature classes within datasets     for dataset in datasetList:         print "Reading: {0}".format(dataset)         name = arcpy.Describe(dataset)         new_data=name.name[num:]         if arcpy.Exists(wk2 + os.sep + new_data)==False:                              arcpy.Copy_management(dataset, wk2 + os.sep + new_data)             print "Completed copy on {0}".format(new_data)                      else:             print "Dataset {0} already exists in the end_db so skipping".format(new_data)     #Clear memory     del dataset  def CopyFeatureClasses(start_db,end_db,num):         #Set workspaces     arcpy.env.workspace = start_db     wk2 = end_db     datasetList = arcpy.ListDatasets()      #for feature classes within datasets     for fc in arcpy.ListFeatureClasses():         print "Reading: {0}".format(fc)         name = arcpy.Describe(fc)         new_data=name.name[num:]         if arcpy.Exists(wk2 + os.sep + new_data)==False:                              arcpy.Copy_management(fc, wk2 + os.sep + new_data)             print "Completed copy on {0}".format(new_data)                      else:             print "Feature class {0} already exists in the end_db so skipping".format(new_data)     #Clear memory     del fc  def CopyTables(start_db,end_db,num):         #Set workspaces     arcpy.env.workspace = start_db     wk2 = end_db     datasetList = arcpy.ListDatasets()      #for feature classes within datasets     for table in arcpy.ListTables():         print "Reading: {0}".format(table)         name = arcpy.Describe(table)         new_data=name.name[num:]         if arcpy.Exists(wk2 + os.sep + new_data)==False:                              arcpy.Copy_management(table, wk2 + os.sep + new_data)             print "Completed copy on {0}".format(new_data)                      else:             print "Table {0} already exists in the end_db so skipping".format(new_data)     #Clear memory     del table      if __name__== "__main__":      start_db = "C:\GIS\PARCEL\Parcel.gdb" #Origin Database     end_db = "C:\GIS\DS.gdb"  #To database     num = 8                                   #number of characters in schema (for example: sde.sde. is 8)     arcpy.Copy_management(start_db,end_db,num)     arcpy.Copy_management(start_db,end_db,num)     arcpy.CopyRows_management(start_db,end_db,num) 

I forgot to post the error.

ExecuteError: Failed to execute. Parameters are not valid. ERROR 000732: Input Rows: Dataset C:\GIS\PARCEL\Parcel.gdb does not exist or is not supported Failed to execute (CopyRows)

[WilliamCraft]

In looking at your error, it seems like you could be trying to insert rows into the GDB itself rather than a feature class or table within the GDB.  Are any of your object classes successfully getting rows copied or does it error out on the first object class?

[JamesCrandall]

Check all of your string references.

This will likely fail:

DS3 = "C:\GIS\DS.gdb"

Change it to:

DS3 = r'C:\GIS\DS.gdb'

[TonyAlmeida]

my initial thought was to back up a database server gdb with feature datasets and feature classes so that's why it was set to insert rows into the GDB itself rather than a feature class or table within the GDB. But i have now changed the DS.gdb to have feature datasets and feature classes but i am still getting the same error. The database datasets and feature classes does get copied and everything looks fine everything row was copied.

I have tried the "r" in my database paths still the same.

[JakeSkinner · Accepted solution]

Hi Tony,

I would recommend just running the Copy function on the entire File Geodatabase.  This will ensure that you are backing up new feature classes, as well as updates that have been made to existing feature classes.

When doing this for SQL Server, it would be best to create a database backup (.bak).  This will backup any versions, archiving, views, etc that the database may have.  Here is an example that uses the PYODBC module.

# imports import pyodbc  #define database db = "STATE"  # define the backup paths server_backup_path = 'c:\\temp\\'  # Connection object (notice that i dont include the database name) conn = pyodbc.connect('DRIVER={SQL Server};SERVER=<server name>;Trusted_Connection=yes', autocommit=True)  def backup_db(conn_obj, db_name, server_backup_path):   try:     os.remove(client_backup_path + db_name + r'_sql.bak')   except:     pass   cur = conn_obj.cursor()   try:     cur.execute('BACKUP DATABASE ? TO DISK=?', [db_name, server_backup_path + db_name + r'_sql.bak'])     while cur.nextset():        pass     cur.close()   except:     print 'cant backup: ' + db_name  backup_db(conn, db, server_backup_path)  # close the connection conn.close()

[TonyAlmeida]

This is great info JSkinns! I am able to create the back up SQL Server .bak but I am not having any luck restoring the .bak file. Every time i try i get an GxGDS box with a big x and an ok button then Arc Catalog crashes... any ideas on why this is happening?

[JakeSkinner]

I'm not sure what is causing this error.  You may want to follow up with Tech Support.  However, you can restore the database by adding another function to the previous code.  Ex:

def restore_db(conn_obj, db_name, server_backup_path):
  cur = conn_obj.cursor()
  cur.execute('RESTORE DATABASE ? FROM DISK=?', [db_name, server_backup_path + db_name + r'_sql.bak'])
  while cur.nextset(): 
      pass
  cur.close()

restore_db(conn, db, server_backup_path)

[TonyAlmeida]

I contacted Tech support and they informed me that the way i went about creating the back file was incorrect it is not a valid back file. They strongly suggest creating a back up through Microsoft SQL Service Manager Studio. They walked me through the process with MSSM which did what i wanted but I would rather create a python script to back up my database weekly.

If i add the following function to the previous code you posted wouldn't that make a back up at the time the code is ran and restore it. I was planning on running the code once a week in case i lost the database and i would have something to restore.

[JakeSkinner]

You can add the second function and make a copy of the script.  One script would be to backup the database, the other would be to restore the database.  You would just need to comment out

restore_db(conn, db, server_backup_path)

for the backup script, and

backup_db(conn, db, server_backup_path)

for the restore script. 

Another option you can use is the SQL Server Agent in SQL Server Management Studio:

http://lakkireddymadhu.wordpress.com/2013/03/01/scheduling-automated-backup-using-sql-server-2008/

[TonyAlmeida]

JSinn3 script works excellent! thank you!

So the only way to restore this back up that was created with this script is to the def restore_db?

def restore_db(conn_obj, db_name, server_backup_path):
  cur = conn_obj.cursor()
  cur.execute('RESTORE DATABASE ? FROM DISK=?', [db_name, server_backup_path + db_name + r'_sql.bak'])
  while cur.nextset(): 
      pass
  cur.close()

restore_db(conn, db, server_backup_path)