Tony's Microsoft Access Blog : backup

Death by Delete - Backups, backups and test your backups

Tony — Thu, 25 Jun 2009 21:49:00 GMT

"One day, a developer was optimizing the database and removing records that MegaPetCo no longer needed. All it took was a single, poorly formed delete query to wipe out each and every row in the database table."

"... but that was the least of the company's worries. The status of its backups was bleaker still: None. Zip. Zilch. Nada."

Death by Delete

Steps to think about when doing a data MDB back up, compact or zip.

Tony — Thu, 18 Jun 2009 22:16:27 GMT

I have a button on my Granite Fleet Manager app which allows the user to do a backup of the backend data MDB. I also have two other buttons which zip and email the backend data MDB to either themselves for backup purposes or to me for support purposes.

However any open forms or reports will lock the back end MDB file so a rename and compact won't happen.

As I do not know which forms and reports the user might have left open when they click on any of the buttons I run through some code that closes all open forms and reports, then run the code doing the rename, compact back to the same file name and, if requested do the zip and email.

' close all forms and reports
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next frm
DoEvents
For Each rpt In Reports
DoCmd.Close acReport, rpt.Name
Next rpt
DoEvents

DoEvents seems to be necessary to give things time to close. But maybe not..

Also note that I do keep a status form open during the above operation. I also do not have any list boxes or combo boxes with the row source set to a query or table referencing the data MDB on the form. And I don't have any open recordset or database variables for performance purposes.

You can double check that this code works by stopping the code just after the above and seeing if the ldb file on the data backend mdb no longer exists

Of course if there are other users in the back end then the rename won't work of course. Also see my Backup, do you trust the users or sysadmins? tips page for more info.

‘Tis The Season To Think About Backups

Tony — Sun, 21 Dec 2008 05:23:34 GMT

An excellent blog posting ‘Tis The Season To Think About Backups. " Just this week a close friend as well as a colleague here at Microsoft had their houses broken into and their PCs with all their photos and videos stolen. Luckily they had done a proper backup. "

The author mentions Outlook and Outlook Express without stating that those files are found in the User Apps folder which is hidden away. In Windows XP it's found in C:\Documents and Settings\ttoews\Application Data while in Windows Vista it's found in C:\Users\ttoews\Application Data. (Or similar.) These days many folks are using Gmail, Hotmail, Yahoo and similar so that may not be of concern to you. Unless, of course, they lose your email for you.

For genuine stories about lost data see the following blog postings Egyptian poet pleads for stolen works to be returned and Have you made a recent copy of your files and photos?

How are your personal backups?

Tony — Sat, 29 Nov 2008 20:53:34 GMT

Including all your photos? And your Outlook and Outlook Express files which are, last I read, stored in a sub folder of your Application Data folder and not your My Documents folder?

"The GpCode family is a dangerous form of malware which can permanently destroy files by encrypting them. The capability for AV products to de-crypt files vary and can't be relied on in all cases, especially when complex encryption techniques are used. "

New GpCode Ransomeware attack encrypts victims files

No, no, no. Do not leave this until tomorrow. Grab a coffee and start copying files to a DVD.

I don't trust the "cloud"

Tony — Tue, 12 Aug 2008 23:59:02 GMT

"Can you trust your data to the cloud? For users of an online storage service called The Linkup, formerly known as MediaMax, the answer turned out to be a resounding "no.""

Loss of customer data spurs closure of online storage service 'The Linkup'

Now apparently this is mostly for backup purposes but still. What if you needed those files?

Do I trust Google, Yahoo, etc with my email, calendar, files? Not only no, but hell no.

Some sys admin somewhere had two screens open, one to a test system and one to a live system. He got confused and delete 15,000 users emails. I can't find that story but found a few others. Security firm trashes customer e-mails. Lycos cleaner bins thousands of emails

Mind you I have a backup plan for my laptop. Every few days I make a password zip file containing all my files. I then burn a DVD and either place in my fire proof (actually one hour fire rated) filing cabinet or my safety deposit box.

<rant>But I'm also a highly adept computer user and know where all my files are stored. Sure folks know about their C:\Documents and Settings\<user name>\My Documents which also frequently has other files which don't need to be backed up on a regular basis such as photos. And I'd say about 5% of users back that up regularly.

And what about photos? Of special family events?

Now how many folks know where their email is stored? Somewhere in the Documents and Settings\<user name>\Application Data folder along with many megabytes of data in other child folders which doesn't need to be backed up.

One retired extended family member asked me to at least retrieve his outgoing emails from a old laptop whose hard drive had died. He explained that his email to his children was basically his diary of the last five years. The hard drive was completely dead. There was nothing I could do. </rant>

... hit the big green TRANSMIT button.

Tony — Wed, 09 Jul 2008 17:53:50 GMT

... hit the big green TRANSMIT button. You know, to transmit it over the stockroom’s intercom. The Free Bird Database. So nice to read that lack of testing backups isn't a problem only in this century. Or indeed the last forty years.

A ten year old backup came in handy today

Tony — Mon, 14 Jan 2008 07:47:40 GMT

I got volunteered into building a volunteer database for the 1997 Alberta Summer Games. (I later made some money off of the database later so my hundreds of hours did turn out to be somewhat profitable. But as I'm halfway through building the database and realized how much work I had left I was a little concerned at the size of the task)

Eleven years later a similar event in that same city decided they'd like to use the ten and a half year old volunteer list to get some volunteers. Turns out I had the Access databases on a CDR in my bank safety deposit box. So I converted to Access 2003, modified a report to suit their needs and sent off the PDF file. If they need it I can easily create an Excel spread sheet to simplify data importing purposes.

I made the following suggestion to the event manager to quickly figure out many of the folks who are still around or have moved out of the community.

I would suggest that you do the following:
1) Flag the volunteers you already have.
2) Hand this list out to the executive asking them to strike out those who have moved or those that they know are still in town.   And ignore those that they don't know
3) However we all know that some executive will take their sweet time going through the list. Thus at your next executive meeting split this list up into six, or so, ten page chunks. Hand them out to each second person at your next executive meeting.   Again have the executive mark those who have moved or those that are still in town. While the meeting is in progress and the person doesn't need to spend much time concentrating on the person babbling on have them go through their chunk of the list. Once they're done have them pass their chunk off to the next person.
(There are two kinds of meetings at Microsoft.   Those meetings in which laptops/tablet PCs are allowed and those where they are banned. There are very few meetings where they are banned. The idea being that at any given moment in time only a few people need the exact details that the speaker is pontificating on. Therefore let the others keep an ear open while they attend to email. Of which they get many.   Thus I figure your executive can time share their attention for such a relatively mindless task as name recognition.)

I did this backup on a 2X Hewlett Packard CDR burner that cost me $600. It died under warranty 9 months after purchase. HP sent me a refurbished unit but it did 14 months after purchase. And as it only had a one year warranty they refused to do anything. I've never quite forgiven HP for that. I'm sure I still have the letter I sent to the HP complaint centre somewhere but they didn't do any follow up.

Backup, backup, backup

Tony — Sat, 10 Nov 2007 02:46:27 GMT

Error blitzes health records - Thousands of hours of work by health board employees have been wiped in a computer glitch.

And what about their offsite backup? Unless there are extenuating circumstances, such as the person just took the job, the IT administrator should lose their job.

"Waikato District Health chief executive Craig Climo said he believed contributing factors taken as a whole were likely to have led to the problem." What fricking "contributing factors"? Of course you can come up with all the excuses you want to.

About ten or twelve years ago I visited an administration office which basically had about a $50 million a year budget to do some work on their network of computers. I introduced myself to the accounting person and we chatted for a few minutes. I asked if they minded if I double checked that the backup was working properly. Please do I was told.

It turned out that the only working backup was the Thursday backup. The expensive backup software required that you "format" the tapes for the software before they could be used. This operation only required a few mouse clicks and about 30 seconds.

Furthermore they had no idea to even look at the backup log. One mouse click would've showed a log with a whole bunch of red flagged entries and the occasional green flagged entry.

Idiot high priced name brand computer consultants employed by a national firm never bothered to explain any of these minor details to the office administrator or accounting staff.

Making copies of your database during the working day

Tony — Sat, 27 Oct 2007 03:23:12 GMT

The following question came up as part of a newsgroup thread.

When you say "make a copy every hour or two", do you really mean that your normal practice during development is to retain multiple separate copies saved at 2-hourly intervals rather than simply overwriting a single backup copy every 2 hours?

Yes, because I'm a paranoid pessimist. And I'm proud of that label. You never know when something really weird might happen and you want a copy of an object from a few hours ago. Or you change your mind and decide you want to go back to an older copy of an object or two.

Within Windows Explorer it's really easy to click on the MDB, Ctl+C, wait a moment for the hourglass to disappear, then Ctl+V. Windows Explorer makes a copy of the file titled Copy of <insert your MDB name here>.mdb. Then Copy (2), Copy (3), etc, etc. Real easy.

At the end of the day or when I accumulate 5 or 10 I zip them up using Winzip's right click context menu into a zip file titled <insert your folder name here> zip. I then rename the zip file to <insert your folder name here> yyyy mm dd.zip i then move that zip to an archive folder, which I include on my backups. In a month or two I those zip files.

Have you made a recent copy of your files and photos?

Tony — Sun, 07 Oct 2007 23:22:50 GMT

And is that copy offsite? Have you made that copy in the last week or so?

And not on a thumb drive either! You have to have at least four or five copies because thumb drives and DVD-Rs can also die or be in a fire.

Daughter of Ted Byfield burned in his home

Despite the physical loss, Mr. Byfield said he was grateful firefighters were able to salvage one of his computers that had manuscripts for a set of Christian history volumes he is putting out next year with a group of people.

"I look inside the ground floor window and there's my computer with two or three years work on it. ... Somehow they saved it, I don't know how. But it could've been a real disaster."

My sister and brother-in-law just lost their laptop hard drive. It had been running really slow at times this last month (They thought they had a virus.) It finally failed with a BSOD (Blue Screen Of Death.) And now I can't retrieve anything from that hard drive. I can see there's a hard drive there but no files.

So they've lost about four years photo's of their winters spent in the Maldives and trips to India, Nepal, Sri Lanka, and their kids growing up. This includes camping in the U.S.A visiting all the standard tourist places such as the Grand Canyon and Carlsbad Caverns.

<sigh>

The Institute for Backup Trauma

Tony — Sat, 04 Aug 2007 20:21:04 GMT

The Institute for Backup Trauma starring John Cleese nicely illustrates some of the basic problems we encounter. And does so very, very well. Although I haven't seen a failure rate of tape backups as high as 50%.

There was just a short story in the newspaper about a woman who left her purse in a hospital washroom after changing the baby's diaper. Stolen from her purse was cash, digital camera and memory stick containing the only copies of photo's of the first month of her baby.

Egyptian poet pleads for stolen works to be returned

Tony — Mon, 09 Jul 2007 19:42:00 GMT

Egyptian poet pleads for stolen works to be returned The relevant paragraph is

"Mersal's office was broken into in the early hours of June 17 and her electronics and black laptop bag were stolen. Inside the bag were both backup copies of her work -- an electronic memory stick and a black notebook filled with Arabic writing scribbled in pencil."

Presumably your corporate backup is in decent shape. How is your personal backup? Is the backup media, likely CDR or DVDR in a different location than your computer? Are you backing up files other than just what appears in the My Documents folder? For example if you use Outlook or Outlook Express those files are somewhere else. What about other software applications settings that are important? What about family photos? Have you tried restoring a few files from your backup occasionally?

I use my own folder for my files including my email and newsgroup postings. I've been using this for at least fifteen years which long predates Windows 95. Every time I install a new hard drive I have to spend five or ten minutes changing all the defaults in various software products such as Word, Excel and others As my email and newsgroup software use INI files to hold their setttings they do not require any registry settings to be transferred.

Several times a week I create an encrypted zipped file of this folder. Zipped it's about 1.1 Gb. This then is copied to my tower computer if I'm at home. I then burn my last three backups onto a single DVDR and put in a fire resistant fileing cabinet. Frequently I will put that backup in my safety deposit box.

Note that a fire proof safe or filing cabinet isn't actually fire proof. It is fire resistant and has a one or two hour rating. Sure, almost all the time fire fighters will get the fire out in that time. Also note that the one or two hour applies to paper. Not plastic. So get those backups offsite!

Classics Week: Immaculate Backup

Tony — Wed, 09 May 2007 20:28:00 GMT

http://worsethanfailure.com/Articles/Classics-Week-Immaculate-Backup.aspx