# Backing up large Historian DB - Ideas?

**URL:** <https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086>\
**Category:** Ignition\
**Created:** [August 3, 2018, 3:37am UTC](https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086 "2018-08-03T03:37:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [August 3, 2018, 3:37am UTC](https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086/1 "2018-08-03T03:37:29Z")

</div>

Hi all,

I have an Ignition Gateway thats been running for a couple of years now, and the Historian DB is quite large now (88GB).  
I am now required to setup a backup server on a separate PC, with the end goal to have SQL replication setup to keep both PC’s databases in sync.

I’ve got most of the partitions as monthly, which are ~8GB each, and have recently changed partitioning to weekly, so the tables are a bit smaller (~2GB).

Doing a test SQL dump of a 4.5GB table took 40min to finish, so if you extrapolate that it’ll take a long time to do a full backup of everything.

What suggestions do people have for doing this? My understanding of replication is that it cannot be started until both SQL servers have the same data, which obviously requires a lot of database dumps and restores.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d1ca6c42fd0492b5160e1285ea53298c5b82c75f.PNG)

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [August 16, 2018, 8:19pm UTC](https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086/2 "2018-08-16T20:19:19Z")

</div>

I have got automatic SQL dumps working that backup entire Databases already, but Historian is too large to use this method.

Is there a way to somehow figure out the most current Historian table partition and run an SQL command to do an SQL dump of just this one table?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [August 16, 2018, 9:16pm UTC](https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086/3 "2018-08-16T21:16:34Z")

</div>

> [@richardNZ15](#):
>
> Is there a way to somehow figure out the most current Historian table partition and run an SQL command to do an SQL dump of just this one table?

You can get the current epoch timestamp, multiply by 1000, and find which record in `sqlth_partitions` has that timestamp between start and end (note: there are potentially more complications if you have multiple gateways storing to the same database). Using that 'dynamic' table name is difficult in a pure-SQL context, though; most database flavors don't support dynamic table names in queries outside of stored procedures.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [September 11, 2018, 8:54pm UTC](https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086/4 "2018-09-11T20:54:41Z")

</div>

I’ve been running my SQLDumps automatically with a script executed by a CRON job on my linux server.  
I could use the following query to get the right table name:

```auto
SELECT pname FROM sqlth_partitions ORDER BY end_time DESC LIMIT 1;

```

but i don’t know how to run an SQL query in a script.

Is it possible to do an SQLDump through Ignition scripting somehow?

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [October 6, 2020, 3:50am UTC](https://forum.inductiveautomation.com/t/backing-up-large-historian-db-ideas/19086/5 "2020-10-06T03:50:07Z")

</div>

To anyone finding this post, i found this other post which details how to execute a script to backup the Historian database, excluding the latest created partition.

> [@MySQL Linux Backup Script](http://forum.inductiveautomation.com/t/mysql-linux-backup-script/10172):
>
> I’ve written the following script to backup the last closed historical partition and assosiated data tables for the Ignition historian on Linux. You can schedule this with cron tab and store the backups on a remote windows share or something pretty easily. #!/usr/bin/python import sys import os import datetime import mysql.connector from subprocess import call from optparse import OptionParser def main(argv): #----------------------------------------------------------------------------------…