Backup your files, always take backups

Location:   http://www.mvps.org/dmcritchie/excel/backup.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Three things are certain:  Death, taxes, and lost data.
You can put them off but you can't eliminate them.

Automatic Backup when File is Saved

Setting up for automatic backup of a file.  (on a single file basis, not global) Now, as to the backups: they are always one save behind the original document.  Excel backups have the original name and the suffix .xlk, and will appear in the same folder as the original. With the Excel backup you can use Autosave and backup simultaneously. roneville@sprintmail.com (2000-11-29)

Immediate solutions -- Backup on Demand -- Excel oriented

Don't forget to check if UNDO will work for you, but you have to use it before you save your file, or before it gets automatically saved like via the Excel Autosave option.  In Excel UNDO is under the Edit menu.  If UNDO won't work because you were in a macro, you might be able to exit immediately without saving, but you lose all of your changes.  Here is a method to make a quick temporary backup of a workbook, especially if you create a button for the macro.  (remember temporary) 
  Sub backup()
    'Don Guillett, misc, 2000-05-08
    Fname = ActiveWorkbook.Name
    ActiveWorkbook.SaveCopyAs "C:\msoffice\personal\BACKUP\ " & Fname
    ActiveWorkbook.Save
  End Sub
To copy to the same directory with a different filename:
    ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & Filename

This version creates a directory, if missing, for the date: 

  Sub backupBYDATE()
    'Don Guillett, misc, 2000-05-08  modified filename for date......
    'documented in http://www.mvps.org/dmcritchie/excel/backup.htm
    Dim dname as String, strTest as String
    dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd")
    strTest = Dir(dname, vbDirectory)
    If (strTest = "") Then MkDir (dname)
    ActiveWorkbook.SaveCopyAs dname & "\BK_" & ActiveWorkbook.Name
    ActiveWorkbook.Save    'also save current file
  End Sub

To make this automatic use the Before_Save event as opposed to Before_Close 
to save a backup each time you are finished but not if you if you decide 
not to save your current changes.   Based on Don Guilett and
Harald Staff (2002-03-07 worksheet.functions).   Install in ThisWorkbook,
modified to allow for several differently named workbooks. 
Note the c:\backups  directory must exist.

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
     Cancel As Boolean)
    Application.DisplayAlerts = False  'based on Harald Staff 2002-03-07
    Dim Fname as string
    Fname = ActiveWorkbook.Name
    If UCASE(ThisWorkbook.FullName) <> UCASE("c:\backups\" & Fname) Then _
      ThisWorkbook.SaveCopyAs "c:\backups\" & Fname
    Application.DisplayAlerts = True
  End Sub
Modifying the filename with "BK_" prefix in the SaveCopyAs avoids accidentally opening and confusing it in the last used list with the good production file.

Harold Staff (2000-09-08 in misc) mentioned that SaveCopyAs added to important Excel workbooks prevents someone from saving with Word and messing up because the command will not function in Word therefore the previous SaveAsCopy version is safe.

Robert Bruce (2001-10-04 in programming) mentioned that you can use  Environ("Temp")  to get the path to your PC's environmental Temp directory.

David McRitchie (2001-10-24 in misc) a variation that includes check for and creation of any required directory based on the filename being created.  Example creates files based on filenames in a selection of cells.  (folderexists, createfolder, savecopyas)

What files should be backed up in Excel

Like anything else your own files should be separated from system, and application software files; since this is in an Excel area will include some files that you might want to make sure that you backup.  You should check the libaries that you find such things as .XLB, .XLS to see if there are other files and if the entire directory should be saved.  My software is on drive H:, my data files are on D: or specific directories on H:. The directories and the drive letter will vary, the above happen to be where mine are.

What about backing up or saving other Office Settings

Save Your Office XP Settings http://office.microsoft.com/assistance/2002/articles/oSaveSettingsWizard.aspx

Office 2000 Save My Settings Wizard http://office.microsoft.com/downloads/2000/o2ksmsdd.aspx

What other files should be backed up

There are many other files.  Basically you want to backup data that you have entered that can't be replaced.  Reinstalling software is often faster than trying to back it up.  A error in 1 byte could destroy a software package, but an error of 1 byte in your data may not be serious unless it had been compressed or encrypted, in which case the entire structure may be at risk with the loss of a byte.

Backup

Windows NT users must also create a current copy of the Emergency Repair Disk (START --> HELP --> ERD).  Purpose of ERD:  backs up Registry, keeps record of current desktop, and other things.  If you ever have to use the ERD and it effects restores from the installation CD, you must also rerun your latest Service Pack.  (on NT service packs supercede previous service packs).

 
I am using DOS batch (.bat) files in a DOS Window to backup my data.  BAT files are invoked instantly just like .exe files so you want to use an editor to change the text content.  There are only two commands besides HELP that are needed to find the directories and to copy directories and files. 
HELP DIR  |more
HELP XCOPY |more
DIR d: /s /on

Backup,  Backup, Backup always take Backups
I create some online backups with a miscbkup.BAT file that included some coding like this, only a lot more extensive.  The drive where most of my data is on is backed up including a duplicate that exists in the d:\miscbkup subdirectories.  A backup has been stored off-site (one is a lot better than nothing).  The following  not a real backup because it is stored on the same physical drive as the original data but it does provide a means to restore a file that I accidententally incorrectly modify or delete.   Lacking a real backup device you can zip the miscbkup folder to a whole lot of diskettes.

          REM --  d:\miscbkup\miscbkup.bat --
          XCOPY  c:\netscape\users\*.htm   d:\miscbkup\c\netscape\users\*.*    /S /D
          XCOPY  c:\*.xlb   d:\miscbkup\c\*.*    /S /D
          XCOPY  d:\myfiles\*.xls   d:\miscbkup\d\myfiles\*.*    /S   /D
          XCOPY  d:\data\*.*   d:\d\data\*.*   /S  /D

It is real easy to run and real easy to restore a file to when you ran the  miscbkup.BAT batch file.  The beauty of using XCOPY is that if the destination directory does not exist it will be created.  The /S includes subdirectories,  and the /D checks the timestamps to see if it needs to be copied.  There are other options and you should check them out.  There is also a list option to see what would be copied without actually doing the copy.

Again you need to have off-site backup in case of disaster in some form such as  tape,  diskettes, zip-drive,  CD-R, or if you are on a network to your server -- I am not referring to your ISP, and those servers had better be backed up.  Generally if you are responsible for the data, it is your responsibility to insure that there is backup.   Don't forget to locate and backup your Email.  Backing up your data only to another hard drive may  not do you much good if you get serious hardware or software problems, you need something stored elsewhere.   If you are a business and you don't have off-site backup and have a disaster that destroys your computer and data you can say good-bye to your business.

The media that you use for backup is not permanent no matter what anyone tells you.  Rainforest data was put on tape backup probably in a temperature controlled backup, the tapes became brittle and unusable and what was left couldn't be used because the tape drives no longer exist.   Remember punched cards.  I'd heard of an operations manager who didn't trust tape and backed up their disk drives to punched cards.  Though they were just a backup think about it, the punch card readers aren't around, those old machines, aren't around, the programs probably aren't around and the people who ran them aren't around.  The point -- your backup is not permanent no matter what anyone tells you.

Backup Considerations using a CD-R for backup
A CD-R can be treated as a big diskette.  If a file is deleted or rewritten on a CD-R the old space is still there and is not reusable.  Files that have been rewritten or deleted cannot be read by ordinary means as they are not accessible through the directory.   A CD-R holds about 650 MB and costs about $2 US.    A CD-RW which can be rewritten on by reformatting the entire CD-RW once filled up holds only about 450 MB and costs about  $5 US.  Since the purpose of a backup is to keep data for awhile, a CD-R works very well.  Sometimes CD-R($1) and CD-RW($2).

My c: drive contains my WinNT system and commercial software programs.  My d: drive contains the files that I put a lot of work into and would lose if all software were reinstalled on a new machine after a disaster.  More specifically my d: drive contains .my data; the data I create such as Excel files, Word files, HTML files, REXX files, REXX macros.   My d: drive contains more data than will fit on a 650 MB CD-R so some folders will be backed up less frequently on another CD-R.    The backup looks very similar to the previous code.  Again this is just an example of some of the code.   Folder dataq1 is not being backed up.  Individual files on the d: drive that are not in a folder are being backed up, followed by selected folders.  I create the basis for this list from  a list of the main directories on the d: drive  --   DIR d:\*.  >> xxxx.bat     and then using my REXX macros to reformat the output, you will probably have to reformat using some other means.
 

REM  -- d:\backupd.bat --
xcopy d:\*.*            f:\bk1\d\*.*  /d
xcopy d:\clubs\*.*      f:\bk1\d\Clubs\*.*     /s /d
xcopy d:\comics\*.*     f:\bk1\d\comics\*.*    /s /d
REM   d:\dataq1\*.*     f:\bk1\d\dataq1\*.*    /s /d
xcopy d:\dcf40\*.*      f:\bk1\d\dcf40\*.*     /s /d
xcopy d:\download\*.*   f:\bk1\d\download\*.*  /s /d
xcopy d:\drivem\*.*     f:\bk1\d\driveM\*.*    /s /d
xcopy d:\excelgrp\*.*   f:\bk1\d\excelgrp\*.*  /s /d
xcopy d:\family\*.*     f:\bk1\d\family\*.*    /s /d
...
xcopy d:\miscbkup\c\*.*   f:\bk1\d\miscbkup\c\*.*    /s /d
xcopy d:\website\*.*    f:\bk1\d\website\*.*   /s /d
xcopy h:\excel2k\*.*    f:\bk1\h\excel2k\*.*   /s /d
...
xcopy d:\images\*.*     f:\bk2\d\images\*.*    /s /d
...
The last entry shown above is copying the files that were previously copied from the c: drive to the miscbkup folder.  Since I am backing up the d: drive, I have no need to create a second backup copy of those files.

Drive F: is the staging area for my backups.  Currently I create 3 CD-RW (bk1, bk2, bk3) with bk1 having the most important frequently changed files.  The letter after that is the drive letter.  The actual backup CD for BK1 does not include BK1 directory -- it would have the directories that have the drive letter (c,d,h) as the major directories.

The types of data that I save from C:  drive include my Excel toolbars,  my Netscape bookmarks,  my Email, things that contain my own material that are on the C: drive.   The MISCBKUP.BAT  file also clears out my Netscape, IExplorer, and AOL cache ust before it finishes.  The cached files are on my c: drive and don't get backed up anyway.   It is a good idea to empty the RECYCLE folders before doing backups.   I don't copy them with my selective XCOPY commands.  The first time I backed up the entire d: drive, and my browser files, I saw some files being backed up unnecessarily including the RECYCLE folders, and cached files.

Currently I begin another CD-R every two months and update it weekly.  With the  /D  option on XCOPY  only the files that got updated are rewritten or added so this can continue for some time if I still had say 20% empty space after the first use.  The CD-R cannot be used on a regular CD-ROM drive until a formatted for such use afterwards -- that takes about two hours.   There is no need to actually make it readable on a regular CD-ROM drive since if you lost all your hardware, you can do this on  another CD-Writer later.   So I wait until it is filled up before making it readable on a normal CD-ROM.

You can use CD-R platters to backup your data and rotate one off-site and continue to use both until one fills up.  When one of them fills up format it to be readable on a regular CD-ROM and retire it as a permanent backup.  Replace the filled up one with a backup to a new CD-R and continue rotating them off-site, on-site.  

Even though originally I did not use a CD-RW because it did not have the capacity of a CD-R, I have switched to using CD-RW because I have to use 3 CD's anyway to backup data, and if there is an error it will rewrite on the CD-RW and continue; whereas, the CD-R will be incomplete at best or completely unusable. for backup, but it just doesn't have the capacity.  Another reason that you might want to use a CD-RW is if you are zipping several files together, the replacement will obviously waste a bigger footprint.

Use of Briefcase

Rob Nobel (2000-05-09 misc) uses 'My documents' folder and any other folders and files such as templates, Custom dictionary, etc. to "My Briefcase", which he stores on a second HD. He creates a shortcut to this and places it on the desktop.

Each time he wants to make sure all files are up to date on the 2nd HD, he right clicks the Shortcut to "My Briefcase" and select "Update All" and presto, in seconds all files are checked whether they are the latest version and if not gives you the option whether you want to update or not.  Which he indiates is very fast and easy, but only suitable for those with that 2nd HD.

Files Restored from a CD backup reported as Read-Only

Although I do not experience this problem with Read-Only files using XCOPY in the WinNT DOS window, many people do have a problem with files restored from a CD backup.  Here are some solutions to remove the read-only attribute on your restored files.

 

  RECOVERY / The Second Half is on Recovery when you fail to have a backup / RECOVERY 

Recovery

Professional Help for Damaged Files

I bring you some feedback from a presentation by someone who restores data as a profession.

Sooner or later you will experience a hard disk crash, so back up your data.  Most of the people who spend the bucks to try to restore data, have very good data backup procedures in place but one critical dataset got caught in between.

Some Basic Facts

  1. If the hard drive suddenly stopped without any indication of any problems, that is a good sign that recovery is an electrical problem and can be repaired.  (connections, wiring, circuit board on drive, etc.)
  2. Data is seldom recovered from a disk crash.  There are outfits that have a customer service department that will tell you they have a 95% recovery rate from hard disk crashes -- they lie.  How would you know if you had a disk crash without paying someone to tell you.  If you had a scratching sound, or a clicking sound your data is doomed.  There is no data once the surface of the disks has been converted to grooves and metal filings (dust).
  3. If the drive can be identified, there is a good chance of recovering some data.
  4. If the drive makes a clicking noise, you have about 20 minutes to two hours of usage left.  Backup your files immediately starting with your critical files first.  DO NOT RUN utilities that write to the disk such as Norton Utilities to fix up disk errors -- it will destroy your data.  Such utilities are designed for repairing minor errors and are not designed for massive failures to rewrite data to another location.

How is data recovered

Data recovery is a large field, and involves not only physically damaged media, but deleted files, files with lost passwords,  A customer's hard disk is removed and cloned so that all work in done on the copy, preserving the customer's original disk as untouched as possible.

How much is this going to Cost?

A lot.

Your recovery cost, if a file can be recovered will be based on such things as.

  1. You will be asked for a list of files that have to be recovered, the bigger the list the more files that will have to be reassembled back into a file.  The bigger the list the more files segments that will have to be put back together; and, of course, the more it will cost to reassemble the pieces back into a file.  The list of files will be what determines success or failure.  Adding more files to the list will, of course, increase the costs.
  2. The bigger the hard drive, the more searching it will take for files; however, restricting to a smaller partition will help keep down data recovery cost.  Files have to be reassembled from pieces scattered all over a drive.  The bigger the drive the more searching.
  3. Don't ask for recovery of programs, a one byte error will render program worthless; whereas, 800 bytes missing/lost/damaged in a data file may be hardly noticeable.  If you have data compression on that is another story, any loss there will destroy the files.

What can the user do to prevent a hard disk crash

What can the user do to prevent a hard disk crash on an already purchased system, where the components have already been selected and assembled.

Nothing.

General Rules to avoid data recovery expenses

  1. Take backups.
  2. Back up your data, don't bother backing up software packages that you simply reinstall.
  3. Put your data on reliable drives, not on the latest drive with the most GB capacity.  All the drives are the same size so if you buy a 8 GB hard drive it will be less intricate and less prone to errors than 17 GB drive, get the picture.

Eliminate unnecessary files

Eliminate unused files for performance such as your browser cache files, your wastebasket (recycle bin), and your temporary files, and then Defrag your hard drives.

Scandisk (chkdsk /f), Open Excel in safe mode

Workbook Recovery

Preliminary Notes: «

Copy your file to another dataset, and work with the copy so you don't mess up the the original if there is anything there.  If the file is small enough make another copy onto a diskette and set that aside away from harm.  Don't run a recovery against your original file, because you may well end up with nothing as a result.

Quick Method to see data «

Even though the intent would be to recover the workbook you may need to see what would have been the visible data immediately.  If you see the data here it gives more hope of recovery of at least something.  This information is part of the method described in Q214253 found from Q142117.  Place one of the following formulas into cell A1 and replicate across and down using the fill-handle.  The longer formula will prevent reporting zeros for cell that are blank or look blank.  (single quotes around file and sheetname, two double quotes together to indicate no text contained)

   =IF(trim('J:\myfolder\[myfile.xls]mysheet'!A1)="","",'J:\myfolder\[myfile.xls]mysheet'!A1)

   ='J:\myfolder\[myfile.xls]mysheet'!A1

The actual instructions have you use File Open to browse to the folder of the damaged file, then cancel out of it, then File, New to create a new Excel file.  This simplifies formula entry and File save for the new file.  The simplied entry of the better formula would then be:

   =IF(trim('[myfile.xls]mysheet'!A1)="","",'[myfile.xls]mysheet'!A1)

Remove the formulas you just created which are dependent on the corrupt workbook.
   Select ALL, Copy (ctrl+c), Edit, Paste Special, values

Attempt to recover the formulas (and constants):  not tested yet
You could get the formulas with something like:
  =personal.xls!getformula('[workbookname.xls]sheetname'!A1)
or perhaps, something that you use change all to fix up
  ="xxyy=" & personal.xls!getformula('[workbookname.xls]sheetname'!A1)

Recovery of workbook, some things that often work «

Password Recovery

One of the most common recovery problems is associated with password protection.  Password removal for sheet level and workbook level passwords with the almost free (postcard thankyou) Excel® Password Remover*.  Finding the actual password takes much longer but free software is available.  Recovery of file protected passworded files can be very expensive.  A list of recovery sites for of passworded sheets, workbooks and/or files.
* http://www.straxx.com/excel/password.html was formerly http://www.elkraft.ntnu.no/~huse/xlpassword.htm

Data Recovery

Commercial recovery programs or services, the following have been mentioned in the Excel groups and may be worth looking into if you have serious problems with corrupted data.  Checking out the costs might be a further incentive to back up your files. 
http://www.officerecovery.com/excel/ Excel Data Recovery Tool, purchase about $150. (formerly conceptdata ?); and,
http://www.datamechanix.com
alternative: for Excel 97 check MS KB Q142117 for recovery tool for XL97 only.

MS KB recovering data -- also see related area below

Related Information -- Excel

I have absolutely no experience with any of these and keep hoping that I never will have to use extraordinary means to recover an Excel file.

When Excel works on a file it renames the original in the same directory to a random 8 character name with no extension.  If Excel was terminated you may still have this file and you can try opening (a copy of) this file in Excel.  When Excel completes normally the new version is saved and the renamed original is deleted.  This is part of the reason one should never keep their original files on floppies, the other reason is that floppies are not very reliable.

How to find more information on recovery:

go to  http://support.microsoft.com/support/search/   to search the MS KB
and search for article ID Q142117, or a keyword (Boolean) word search for "recover data", (1) Excel, (2) keyword, (3) recover data, (4) Full Text, Titles with Excerpts.  In the MS KB, "AND" is default.  Logical operators AND, OR, AND NOT can be used in upper or lowercase, parenthesis and symbols are not understood by MS KB.
Check Google Usenet Advanced Search for "Corrupted Files" in newsgroups:  microsoft.public.excel.*

Some more links on recovery of corrupt Excel files

Related Information - beyond Excel

Related Information -- Backup

Related Information -- File Recovery

Related Information -- Outlook Express 5.0

Related Information -- NT

Related Information -- Outlook Express

Related Information -- HP CD Writers

      From http://www.hewlett-packard.com search:  read-only  +cd-writer  +copied

Interesting Information -- Hard Drives (HDD)

Fun Stuff

This page was introduced on April 6, 1999. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com >