Safest way to backup a table - thinking of delimiters, quoted text, with unpredictable data (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 01:59
Joined
Mar 14, 2017
Messages
8,738
So we have a couple Sharepoint lists. Because we have so many hands in the pot (unfortunately), my manager is (understandably) worried and wants me to back them up nightly. Also unfortunately, I don't have a lot of control over the input data--Meaning specifically, I can't prevent people from entering quotes, tabs, weird symbols, carriage returns etc.

My preference is kind of to export them to a flat, delimited file. I'm writing my own recordset/textstream code because I don't feel TransferText gives me enough control. I'm actually open to TransferText if I learn something that makes me realize it will be good enough.

My question is something that has always been a classic dilemma for me. "What is the SAFEST - most FOOL-PROOF way to delimit this file?"

I generally like to use odd characters, like pipe to delimit columns, caret to quote text. But regardless of whether I go typical (comma, quote) or weird symbols, what's to stop someone from having any of those characters in the natural data??

Is there really any guideline/answer to this question, or is it just, the more cryptic and least likely to occur in the natural data, the better off you are?
(Which is to say, I'd do something really weird, like write code to create a text file with columns delimited & quoting by multi-characters).

Am I over complicating it? If you think "yes", please be specific....as in, what do you suggest that solves the problem of, What if that delimiter occurs naturally in the data?
Any feedback welcome--thanks.
 

Minty

AWF VIP
Local time
Today, 08:59
Joined
Jul 26, 2013
Messages
10,353
I have used the pipe character | or ¬ for years and never seen either fail in normal text data, the second one(can't remember what it is called "Not Sign"?) even more unlikely to in my humble opinion.

I guess the real answer would be to replace all occurrences you found of your potential delimiter with a random block of replacement text that wouldn't possibly be present like Pm7DingDongDelimiter~^ and then reverse the process if you ever need to restore?
 

Isaac

Lifelong Learner
Local time
Today, 01:59
Joined
Mar 14, 2017
Messages
8,738
I have used the pipe character | or ¬ for years and never seen either fail in normal text data, the second one(can't remember what it is called "Not Sign"?) even more unlikely to in my humble opinion.
Ok, thanks. I'm used to using slightly odd things (to the point of the pipe delimiter but not the other one you mentioned), but then I also had control over the input data and could design accordingly. Here I don't have that holistic control so it got me thinking.

I guess the real answer would be to replace all occurrences you found of your potential delimiter with a random block of replacement text that wouldn't possibly be present like Pm7DingDongDelimiter~^ and then reverse the process if you ever need to restore?
It should be something that makes my successor smile, like DelimitThis!

Seriously, though - thanks, good to hear others experience. I definitely will do something more creative than a saved export spec, then, which only allows for a single character.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
If you're worried about handling what users might "type" into the data, then maybe you can use a delimiter that they can't type in (or would have to really go out of their way to do it).

What I am more concerned about is why are you pulling the data out of SP for a backup? How would you put it back in if and when you need to restore the data? I would have thought you would do the backup within SP. Is that not possible (not an option)?

Just curious...
 

Isaac

Lifelong Learner
Local time
Today, 01:59
Joined
Mar 14, 2017
Messages
8,738
If you're worried about handling what users might "type" into the data, then maybe you can use a delimiter that they can't type in (or would have to really go out of their way to do it).
That's a good idea - like something that requires Alt+Keycode. I think that aligns with Minty's suggestion of that symbol that looks like a lock key.

What I am more concerned about is why are you pulling the data out of SP for a backup? How would you put it back in if and when you need to restore the data? I would have thought you would do the backup within SP. Is that not possible (not an option)?
So, a lot of our Sharepoint options are corporate bureaucracy that are buried 10,000 feet deep in administrative request processes with the sharepoint gods, they are very hard to get to and requests can take weeks. I am worried that much of the restore options fall within that category, at my company.

Then we have version history, but that's record-level, with a restore option.

Truthfully, there is probably some of this that I simply am not sure about and don't know--it is possible that something exists which makes this unnecessary, but since it was pretty easy to create a backup & he asked, I just forged ahead.

How would we restore? It would be a fairly easy insert, into the Access rendition of the linked SP list. With the exception of People fields, which we'd have to insert to a "People-Text" field, and then create a SP view to see the real People field and the Text field side by side, (in datasheet view) and then paste from one to the other, which seems to work pretty well.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
That's a good idea - like something that requires Alt+Keycode. I think that aligns with Minty's suggestion of that symbol that looks like a lock key.


So, a lot of our Sharepoint options are corporate bureaucracy that are buried 10,000 feet deep in administrative request processes with the sharepoint gods, they are very hard to get to and requests can take weeks. I am worried that much of the restore options fall within that category, at my company.

Then we have version history, but that's record-level, with a restore option.

Truthfully, there is probably some of this that I simply am not sure about and don't know--it is possible that something exists which makes this unnecessary, but since it was pretty easy to create a backup & he asked, I just forged ahead.

How would we restore? It would be a fairly easy insert, into the Access rendition of the linked SP list. With the exception of People fields, which we'd have to insert to a "People-Text" field, and then create a SP view to see the real People field and the Text field side by side, (in datasheet view) and then paste from one to the other, which seems to work pretty well.
Since you have versioning turned on, I think that would mean that when you "restore" your homemade backup, you will lose all those history. No?
 

Isaac

Lifelong Learner
Local time
Today, 01:59
Joined
Mar 14, 2017
Messages
8,738
Since you have versioning turned on, I think that would mean that when you "restore" your homemade backup, you will lose all those history. No?
Well, that's a good question.

I guess it depends on, What (bad) event are we anticipating, and what will be the nature of our Restore process.

To be honest...I think we are anticipating accidental deletion of records, due to some people having Access access to it (and it's just so easy to accidentally bulk delete records when linked to Access!). In that case, our history is gone anyway, and we Insert.

However, if the bad event occurs which is people incorrectly Updating records, then you have a good point--I should probably in that case NOT delete-and-replace, (unnecessarily removing history), but rather, run an Update query based on last night's backup.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
In that case, our history is gone anyway, and we Insert.
Not necessarily. You might verify this, but I'm hoping you will find those deleted records in the Recycle Bin.

Please remember there are two levels of RB in SP, so check both locations.
 

Isaac

Lifelong Learner
Local time
Today, 01:59
Joined
Mar 14, 2017
Messages
8,738
Not necessarily. You might verify this, but I'm hoping you will find those deleted records in the Recycle Bin.

Please remember there are two levels of RB in SP, so check both locations.
Ok, yeah....you're right. But I have to say we are constantly running close to our space allottment (and constantly having to ask for more - it's really a matter of 10 subsites sharing the space requirement of one site, so very hard to 'own' our space management)....and we empty that Bin pretty frequently. But I guess that's true....that's one possibility, that if nobody has emptied the rb, they might be there.

Probably I'm still going to be asked to do this backup "just in case"-kind of thing. Ya know--backing up in case all the other things that ought to be or happen, aren't or don't happen. : )
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
Ok, yeah....you're right. But I have to say we are constantly running close to our space allottment (and constantly having to ask for more - it's really a matter of 10 subsites sharing the space requirement of one site, so very hard to 'own' our space management)....and we empty that Bin pretty frequently. But I guess that's true....that's one possibility, that if nobody has emptied the rb, they might be there.

Probably I'm still going to be asked to do this backup "just in case"-kind of thing. Ya know--backing up in case all the other things that ought to be or happen, aren't or don't happen. : )
Of course, nothing wrong with covering all the bases. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,970
In a corporate environment, a user department would NEVER be responsible for managing the backup/restore for production data. That would fall to the ID department.

Where are you saving the backup?
Who is controlling when it gets done?
More important, who is controlling a restore?
If I were to choose a CSV format, I would use the pipe delimiter and TransferText handles that without a problem.
If I had control over the backups, I would back up to Access and avoid the other issues.
 

Isaac

Lifelong Learner
Local time
Today, 01:59
Joined
Mar 14, 2017
Messages
8,738
I am the IT dept in this case. I'm saving it to a network drive. I'm controlling when it gets done and I would be able to fully control the restore, too.

If I had control over the backups, I would back up to Access and avoid the other issues.
That's a very good point - and a helpful reminder, I might actually switch it to that. Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,970
Having the backups as .accdb's might come in handy for other things.
Do the backups a couple of times a day if you can.
 

Users who are viewing this thread

Top Bottom