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

[Isaac]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.

 

[Isaac]

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