# Undo Truncate Database

**URL:** <https://forum.inductiveautomation.com/t/undo-truncate-database/44129>\
**Category:** Ignition\
**Created:** [March 4, 2021, 10:14pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129 "2021-03-04T22:14:01Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 4, 2021, 10:14pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/1 "2021-03-04T22:14:01Z")

</div>

Is there any way to undo a truncate command issued by the Ignition database browser? Is there any cached data that could be retrieved?

---

<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:** [March 4, 2021, 10:25pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/2 "2021-03-04T22:25:31Z")

</div>

It’s up to your database software, but generally the answer is no. You might have periodic backups you could rollback to, but only if previously enabled on your DB.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 4, 2021, 10:35pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/3 "2021-03-04T22:35:29Z")

</div>

yep, that is what I was afraid of. Live and learn 😢

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 5, 2021, 1:04pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/4 "2021-03-05T13:04:50Z")

</div>

I reconfigured all my database connections with a new user profile that does not have `DROP` privileges so I can’t mistype a truncate command again. 🤦‍♂️

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 9, 2021, 3:00pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/5 "2021-03-09T15:00:54Z")

</div>

I submitted to the ideas forum: Add ‘Are you sure?’ prompt and/or some form of data caching for truncate/drop queries to protect against accidental data loss.  
[Database Query Browser Protection | Voters | Inductive Automation](https://ideas.inductiveautomation.com/ignition-features-and-ideas/p/database-query-browser-protection)

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [March 9, 2021, 3:11pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/6 "2021-03-09T15:11:18Z")

</div>

If you use transactions you can rollback up to the point when you commit (at least on PostgreSQL and MS SQL)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 9, 2021, 3:14pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/7 "2021-03-09T15:14:34Z")

</div>

I assume you are talking about `system.db.beginTransaction`… this is from scripting, I’m talking about when using the Database Query Browser.

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [March 9, 2021, 3:23pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/8 "2021-03-09T15:23:13Z")

</div>

That sounds like a convenience wrapper so you don’t have to `system.db.runQuery("BEGIN TRANSACTION")`

MS SQL `BEGIN TRANSACTION` docs: [https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15)

In the database query browser

```auto
BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT;  

```

or like

```auto
BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 31; --oops!
ROLLBACK;  

```

It’s helpful to do a `SELECT` before committing to check your work.

Obviously you’ll have to run `COMMIT` or `ROLLBACK` as a separate query to get the benefit

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 9, 2021, 3:33pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/9 "2021-03-09T15:33:00Z")

</div>

I would still like to see something like a `rollback` button  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/1/d157d423fd68c45636b25273178ba3a672dfbe75.png)

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [March 9, 2021, 3:51pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/10 "2021-03-09T15:51:01Z")

</div>

Then you also need a begin transaction and a commit button and Ignition now has to keep up with your transactions. What if you start a transaction, close the database browser, come back later and do some more stuff, then hit rollback thinking you’re going to roll back the last query and instead you lose a day’s work?

At that point why not just do transactions in SQL? This is SQL 101. SSMS doesn’t even have a begin/rollback button. I see this as a difficult-to-implement feature with limited upside

Sure an undo button would be nice but that’s just not how SQL works

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [March 9, 2021, 3:54pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/11 "2021-03-09T15:54:10Z")

</div>

Sorry about your data btw. Been there, done that. It’s how I learned the value of transactions 😅

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 9, 2021, 4:05pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/12 "2021-03-09T16:05:37Z")

</div>

> [@chasondeshotel](#):
>
> It’s how I learned the value of transactions

As well as the value of running a SELECT in place of DELETE to insure you're actually deleting what you want to, prior to actually deleting it.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 9, 2021, 4:17pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/13 "2021-03-09T16:17:41Z")

</div>

> [@lrose](#):
>
> the value of running a SELECT in place of DELETE

Does that makes sense when using `truncate`? With truncate you want to delete everything and reset the table, so most people like me, **unwisely** do not review the table before the command. I typed the wrong table name, and poof data gone.

> [@chasondeshotel](#):
>
> It’s how I learned

Why should someone need to make an irreversible mistake to learn the lesson?

Overall, I think you guys are right. But I think there is value in having a better way to log/start transactions in the DQB to protect noob mistakes.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 9, 2021, 4:49pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/14 "2021-03-09T16:49:42Z")

</div>

> [@dkhayes117](#):
>
> Does that makes sense when using `truncate` ?

Absolutely!

Had you run, `SELECT TOP 1000 * FROM wrongTable` prior to the `truncate` you probably would have seen it was the wrong table.

> [@dkhayes117](#):
>
> I think there is value in having a better way to log/start transactions in the DQB to protect noob mistakes

How does Ignition in general know the user's experience level? How does the DQB know you don't mean to run against that table? If it asked you to confirm, in the moment would you have really reviewed what you were doing, after having been annoyed at Ignition constantly asking you if you're sure?

I used to use this software package called Unigraphics (I'm sorry for anyone who knows what that is), when ever you would close, weather your file was dirty or not, it would ask you if you were sure you wanted to exit. Of course I'm sure, that's why I clicked the X button. Problem was, you were conditioned to just click yes, the one time you hadn't clicked save in 2 hours, poof there went your work. Super frustrating.

I feel like this would be much the same in that respect. I still make what I would consider to be beginner level mistakes, and I've been at this for nearly 20 years (boy that makes me sound old 🤣)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 9, 2021, 5:23pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/15 "2021-03-09T17:23:10Z")

</div>

Using transactions in the query browser cannot be trusted, as each query is run from the pool of connections. I don’t think you can be sure any actual query will run in the connection that has the transaction started, nor that a rollback would run in the connection that has uncommitted operations.

Sorry. Truncate is dangerous. Always will be, I suspect.

---

<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:** [March 9, 2021, 5:38pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/16 "2021-03-09T17:38:11Z")

</div>

There are two kinds of developers in the world: those who’ve accidentally deleted something with a SQL command, and those who _will_ accidentally delete something with a SQL command.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 9, 2021, 6:04pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/17 "2021-03-09T18:04:54Z")

</div>

> [@pturmel](#):
>
> Sorry. Truncate is dangerous. Always will be, I suspect.

🤣 You're probably right. However, to me it is like saying

> I've bought the world's fastest car, but I can't order it with airbags. Speed is dangerous, and always will be.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [March 9, 2021, 6:37pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/18 "2021-03-09T18:37:15Z")

</div>

SA,Root,Administrator,DBOwner, etc… There are reasons that everyone in charge of security are VERY mindful of handing out rights above someones head. Raw database access on a production server should be treated on the same level as Root or Administrator.

We have all made those kinds of mistakes, best to learn to fear the major altering commands in a production environment.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 9, 2021, 6:47pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/19 "2021-03-09T18:47:25Z")

</div>

> [@MMaynard](#):
>
> We have all made those kinds of mistakes

Exactly, why is it so wrong to ask for the query browser to cache table data before truncation is invoked. It would only persist as long as the browser window is open, then memory could be freed on browser close? You could even have an option to turn on/off or alter caching behaviors.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 9, 2021, 6:50pm UTC](https://forum.inductiveautomation.com/t/undo-truncate-database/44129/20 "2021-03-09T18:50:27Z")

</div>

And when the table has 1TB of data? When truncating, the client isn’t even seeing the data that is discarded. So too with DELETE FROM–all the action is in the DB. You need to be asking your DB supplier to offer this type of undo, not IA.

[Next page](https://forum.inductiveautomation.com/t/undo-truncate-database/44129.md?page=2)