topic Re: Backing up databases in Python Questions

Backing up databases

TonyAlmeida — Mon, 16 Dec 2013 16:34:09 GMT

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)

Re: Backing up databases

WilliamCraft — Mon, 16 Dec 2013 16:47:33 GMT

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?

Re: Backing up databases

JamesCrandall — Sun, 12 Dec 2021 06:32:19 GMT

Check all of your string references.

This will likely fail:

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

Change it to:

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

Re: Backing up databases

TonyAlmeida — Mon, 16 Dec 2013 17:15:35 GMT

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.

Re: Backing up databases

JakeSkinner — Mon, 16 Dec 2013 17:18:44 GMT

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()

Re: Backing up databases

TonyAlmeida — Mon, 16 Dec 2013 20:07:39 GMT

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?

Re: Backing up databases

JakeSkinner — Sun, 12 Dec 2021 06:32:22 GMT

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)

Re: Backing up databases

TonyAlmeida — Tue, 17 Dec 2013 15:48:02 GMT

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.

Re: Backing up databases

JakeSkinner — Tue, 17 Dec 2013 16:15:31 GMT

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/

Re: Backing up databases

TonyAlmeida — Sun, 12 Dec 2021 06:32:24 GMT

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)

Re: Backing up databases

TonyAlmeida — Thu, 24 Jul 2014 20:49:05 GMT

A little old thread but i am trying to figure out how to save over my network.

I currently have tried the using the UNC path but no luck.

like the following but no luck.

server_backup_path ='\\\\ccdept\\dsd\\GIS\\'

Re: Backing up databases

ChrisMathers — Fri, 25 Jul 2014 14:20:53 GMT

Some functions trip on UNC paths on occasion so it may be easier to save it locally to the database and move it with shutil.copy to the network location. Also, instead of trying to escape the slashes just flip your slashes forward, or wrap the whole thing in triple quotes and only give it what slashes you really want: """\\ccdept\dsd\GIS"""

Re: Backing up databases

TonyAlmeida — Fri, 25 Jul 2014 17:42:11 GMT

Shutil.copy worked out best.

Thanks!

Re: Backing up databases

JohnFell — Fri, 25 Jul 2014 19:56:52 GMT

Tony,

You may also look into a maintenance plan that is available with SQL Server. You can add a backup database task to the plan and just schedule it. See this microsoft developer network document.