Database Backup Issue

[sandy22]

Hi All.

I am new to access so not sure if this is normal but I am creating the backup of a database using Save Database As Back Up Database as I have to delete some data. But when I check the data gets deleted from the back up database as well. How can I create a backup without the data being deleted in the back up. Any help is appreciated.

Thanks

 

[theDBguy]

Hi. A simple copy and paste from the Windows Explorer works just as well. But if you want a code solution, that's available too.

Code Snippets

 

[plog]

Is the data in tables that exist in the database or linked tables? Linked tables don't actually hold the data, they hold the information needed to link to the external file that table is in. So, you need to back up the table the linked table actually resides in to copy that data.

 

[sandy22]

So I created the back up of both the database and the linked table database. But when I delete the data in the database it still is being deleted from the back up database

 

[theDBguy]

So I created the back up of both the database and the linked table database. But when I delete the data in the database it still is being deleted from the back up database

Hi. Since we cannot see what you're actually doing, maybe a step-by-step recount of what you did would help us help you figure out what's going on. Just a thought.

 

[sandy22]

Ok so here's what I have done:
I have a Monthly_Report database which has a table rawdata linked to DataRep database.
I need to back up the database and then delete some data from rawdata table So I did the below:
Created back up of Monthly_Report database
Created back up of DataRep database.
Deleted data from rawdata table

And checked the back up to see if that had data but the rawdata table in Monthly_Report database is empty as well

 

[theDBguy]

Ok so here's what I have done:
I have a Monthly_Report database which has a table rawdata linked to DataRep database.
I need to back up the database and then delete some data from rawdata table So I did the below:
Created back up of Monthly_Report database
Created back up of DataRep database.
Deleted data from rawdata table

And checked the back up to see if that had data but the rawdata table in Monthly_Report database is empty as well

Okay, thanks. I think that makes sense. You said you had a table (rawdata) linked to another database (DataRep). If so, the data in that linked table is actually stored in the DataRep database. So, if you made a backup copy of the DataRep database, what did you call it? Try to open it and see if the data is still there. The reason why the backup copy of Monthly_Report is also empty is because it is also linked to the original backend database (DataRep). That is fine. If you want to link the backup frontend database (Monthly_Report_Bak) to the backup backend database (DataRepBak), then you can just use the Linked Table Manager for that (select the "always prompt..."). Cheers!

 

[sandy22]

Thank you, It worked !!!

 

[theDBguy]

Thank you, It worked !!!

Okay, glad to hear that. Good luck with your project.

 

[Pat Hartman]

I would rethink this process. It is very "Excel-like" and not at all how you would handle the task in a relational database. Just because you have an Access BE rather than a SQL Server BE doesn't mean you should treat it as you would a spreadsheet.

How would you do this if you couldn't copy the BE and "save" it? Most likely, you would need to use a delete date rather than a physical delete so that all data remained in the tables and you would use date arguments in your queries to look at the data at a point in time.

 

[plog]

...you would use date arguments in your queries to look at the data at a point in time.

Agree 100%. You start treating this thing like a database and your life becomes so much simpler. A little prep work and you can eliminate this monthly backup process.

 

[sandy22]

I would rethink this process. It is very "Excel-like" and not at all how you would handle the task in a relational database. Just because you have an Access BE rather than a SQL Server BE doesn't mean you should treat it as you would a spreadsheet.

How would you do this if you couldn't copy the BE and "save" it? Most likely, you would need to use a delete date rather than a physical delete so that all data remained in the tables and you would use date arguments in your queries to look at the data at a point in time.

I am just learning databases now so I understand the first part you said and it makes sense but I didn't get the delete date part. Could you explain so may be I can try that ?

 

[The_Doc_Man]

Typically, you don't delete data, you just time-tag it with a date (call it ObsolDate, perhaps) after which it is considered obsolete. Then you put some insane future date for things that haven't been declared obsolete yet. You can put dates in Access up to 31-Dec-9999 and it will still work. Don't add one to that date, though, because that would cause the date routines to barf. Then you can write a query to show you data that is not yet obsolete by using something similar to:

SELECT bunch-of-data FROM my-historical-table WHERE [ObsDate] > Date() ;

That would only show you the records for which an "obsolete" declaration hasn't occurred yet. And all you have to do is update the ObsDate field with a particular date of obsolescence when that record becomes too old.

 

[Solo712]

I would rethink this process. It is very "Excel-like" and not at all how you would handle the task in a relational database. Just because you have an Access BE rather than a SQL Server BE doesn't mean you should treat it as you would a spreadsheet.

How would you do this if you couldn't copy the BE and "save" it? Most likely, you would need to use a delete date rather than a physical delete so that all data remained in the tables and you would use date arguments in your queries to look at the data at a point in time.

I agree. Best thing is to save the whole BE of the database, before deleting anything from its tables. Using data stamp in the name of the saved copy will help retrieve the correct backup copy to which you may want to roll back.

Here is an example how to back up the whole BE file with a date stamp:

Public Sub Backup()
   Dim SelectedDir As String, srcepath As String, BE_path as String, BEname as String, destpath As String, msg As String
   Dim ofd As FileDialog, SQLstr As String
   Dim i As Long
 
   BE_path = ".........initial full path to Back End"
   SelectedDir = ""
   Beep
 
   Set ofd = Application.FileDialog(msoFileDialogFolderPicker)
 
   With ofd
    .Title = "Select Folder for XXX Backup "
    .InitialFileName = BE_path & "\Backups\"
    .AllowMultiSelect = False

    If .Show <> -1 Then GoTo Bckup_Exit
       SelectedDir = .SelectedItems(1)
   End With
 
   If Len(SelectedDir) = 3 Then
     SelectedDir = Left(SelectedDir, 2)
   End If
 
   BEName =  Replace(Application.CurrentProject.Name, "_FE", "_BE")
   srcepath = BE_path & "\" & BEName
   destpath = SelectedDir & "\" & Format(Now, "YYYY-MM-DD HHMMSS") & "_" & BEName
   Beep
   i = MsgBox("Ready to back up " & srcepath & vbCrLf & _
               "to " & destpath, vbOKCancel + vbQuestion, "Back up database ?")
   If i = vbCancel Then Exit Sub
   On Error GoTo Err_Backup
   '
   FileCopy srcepath, destpath
   '
   MsgBox "Database successfully backed up ! ", vbInformation
Bckup_Exit:
   Set ofd = Nothing
   Exit Sub
Err_Backup:
   MsgBox " Backup failed ! -> " & Err.Number & "-" & Err.Description
   Resume Bckup_Exit
End Sub
'------------------
Public Sub FileCopy(Srce As String, Dest As String)
  Dim fs As Object

  Set fs = CreateObject("Scripting.FileSystemObject")
  fs.CopyFile Srce, Dest
  Set fs = Nothing
End Sub

Hope that helps.

Best,
Jiri

 

[Pat Hartman]

I agree. Best thing is to save the whole BE of the database, before deleting anything from its tables.

How is this method NOT Excel-like? Yes it copies the data at the correct point in time but it still treats the Access data as if it were a plain file. What would YOU do if your BE were SQL Server and you could not just make a copy of it at a point in time?

 

[Isaac]

I have worked for several companies, major banks and gov agency, and they certainly did back up their sql servers, and regularly. It is legitimate to back up a BE Access database. Things happen. Backing up every so often is not unreasonable at all.

 

[The_Doc_Man]

For some databases I have had to work around, the issue was to tell the DB engine to perform its own backup in a self-consistent way. For instance, we would put ORACLE into a backup mode, take our data snapshots, and then release the flood-gates. While in backup mode, you couldn't update the backend tables but the transaction log files were in a different place. What ORACLE did was look in the logs to see if a transaction had been run against a table currently in backup mode. If so, it would copy the data, replay relevant parts of the transaction, and give you the current value. Then when you finished the backup, it began replaying the saved transaction files until everything was caught up, after which it released the space taken up by the log files. Obviously, you ran this at a time of day that minimized the incoming transaction requests.

The problem, of course, is that Access doesn't have a mode to do this by itself. To prevent "smear" you have to enter Access in Exclusive mode so that you know exactly what everyone else is (not) doing. The trade-off is you can do this piecemeal but you have to hope that the tables don't have some action underway to desynchronize your "snapshot." Or you can gain exclusive rights, make a whole-file copy (which is quicker that a programmed copy), and return everything to normal ASAP.

 

[isladogs]

How is this method NOT Excel-like? Yes it copies the data at the correct point in time but it still treats the Access data as if it were a plain file. What would YOU do if your BE were SQL Server and you could not just make a copy of it at a point in time?

Not sure what point you are making Pat,
You can make a backup of a SQL Server BE at a 'point in time'.
In fact I have code to do that built into all my commercial Access FEs that have a SQL BE

 

[Pat Hartman]

It is legitimate to back up a BE Access database.

Of course it is but applications do NOT initiate the backups. The backups are scheduled sometimes as much as several times a day if the loss of data entered would be difficult to recover from. The backups are NOT done to preserve data at a point in time.

 

[The_Doc_Man]

Pat, beg to differ on a fine point. With the Navy, we had a remote replication system, which is how when Katrina pretty much incapacitated our main site, we had data ready to go in our standby site, using data replication. Essentially we did remote RAID-1 type mirroring, but to make that work we had to make the database quiescent so that we could make a point-in-time copy from which to resume operations elsewhere. We learned (the hard way) that ordinary backups were useless for ORACLE. In fact, the sales rep for the replication system swore that it would work without quiescing the system first, but I looked at how it worked and knew that it would not be OK. So (let's call the sales rep "V") V and I made a bet, with me being on the "won't work" side. I won a steak dinner from Cattleman's Restaurant in Ft. Worth because of that. V paid up and I had a GREAT K.C. strip.

For databases that use the "Instantiation Sequence Number" concept as a sort of "Autonumber" that tells you to which generation a table belongs, if you cannot find tables with the same ISN, they aren't synchronized and (at least in the case of ORACLE) you won't "mount" the database. For our tests in October of 1992, I won the bet. But by late August, 2005, we had perfected the process. So when Katrina headed our way, I went to the office on Saturday and triggered a full-on system shut-down followed by full replication. I drove with my family Sunday, and by Monday morning the U.S. Navy Reserve Headquarters Support system was up and running from Ft. Worth - because we took a "point-in-time" remote replication.

If the database engine uses the ISN method of synchronization, I'm pretty sure that your backups will have to be adequate to get you to a "point in time" because of the ISN requirement. Unless ORACLE has moved away from that lately.