Archiving databases - split database

[jaryszek]

Hi Guys,

i prepared 2 ways of archiving my split database.

First: copy back end using:

Application.CompactRepair mehod

Second:



code like here:

Private Sub ButtonArchDaty_Click()

Dim oDB As dao.Database
Dim sFile As String
Dim NameString As String
Dim strString As String


DoCmd.SetWarnings False


If IsNull(Me.txtDateStart) Or IsNull(Me.txtDateEnd) Then
    MsgBox "Nie wybrano prawidłowo przedziału dat"
End If


NameString = InputBox("Wpisz nazwę pliku", "Nazwa")


sFile = "C:\Users\ljar01\Desktop\Makro Braki Access\Nowy\Archiwum\" & NameString & ".accdb"


On Error Resume Next
Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)


If Err.Number <> 0 Then
    MsgBox "Wybrany nazwa pliku już istnieje, wybierz inną"
    End
End If
''DoCmd.CopyObject sFile, , acTable, "tbl_Braki"


strString = "SELECT [QryMakeBackUp].* INTO tbl_Braki IN " & "'" & sFile & "'" & " FROM [QryMakeBackUp]; "


DoCmd.RunSQL strString


DoCmd.SetWarnings True


MsgBox "Archiwizacja przebiegła pomyślnie"


''accessApp.DBEngine.CreateDatabase "D:\tblImport.accdb", DB_LANG_GENERAL


End Sub

So i am archivising query.

It is ok.

Now, please imagine the problem:
database is working 3 months or 0,5 year or year.
And it is very, very large.

I can do compact database but earlier or later - there will be a problem with database's size .
Now how to solve this - i am thinking of creating back-up files for for example 1 quarter, or half an year and delete records in my main back-end split database.

So user can choose dates and within query there will be a back up done. And now user should have possibility to open archive with specific dates (
i am assuming that user doesnt have to have possibility to change archive records, but if it is a good approach?)


thank you for all your tips,
Best Wishes,
Jacek Antek

[jaryszek]

Anyone ?

Jacek

[aytee111]

This would be something that you would define. Determine the time period - e.g. can the database last a year? More? Less? How long will it take to reach 2gb in size? From those answers you can figure out how to design this. If your back-end will reach 2gb in less than one year I suggest you put the data on to SQL Server instead. Otherwise, do you need one back-end a year, or ... ?

[jaryszek]

Hi my friend,
thank you for your help.

If you are saying about SQL server and my company only use free tools (expect is one licence of Access), it can be SQL Server Express?

Maybe should I be prepared to create a back - up using the simplest way in the world :
copy and paste into new folder Access back-end and join to it new Access front-end.

Maybe it is also the good solution ?

Best Wishes,
Jacek Antek

[aytee111]

Using Access to store all data, then yes having multiple back-ends which you would link to the front-end would make sense. But again, if you are creating such a large number of records then some version of SQL Server would be more appropriate.

First, however, define your requirements, make your decisions based on actual numbers.

"Back-up" is not the right word to use here, I would hope that you already do a back-up of your back-end at least once a day!

[jaryszek]

Thank you aytee111 for your help.
I will think about that.

I am going to create automatically back-up of my back-end once a day.
I am wondering how many back-ups should be saved ob my disc.

Do you have maybe some rule to do it in the best way? For example i am creating 100 back-ups and starting to remove the oldest in order to save a space on disc ?

Best Wishes,
Jacek

[aytee111]

How stable is your network? If something goes wrong and you have to go back 100 days, will the data be of any use? I worked at a place where the network was so unstable that I was doing three back-ups a day! My personal rule is one for each working day which gets overwritten (Monday's file will get overwritten every Monday), then 5 weekly files (1 thru 5), then one for each month.

Then an off-site copy. This could be daily copying of the database(s) on to a thumb drive. This is called disaster recovery planning - if a fire destroys the office, that would be worst-case. Some people use the cloud for this, however a fire could destroy their servers too! Plus it is then public information. How important to your company is the information that the database stores? That is what you must plan for.

[jaryszek]

Thank you aytee111 for you help and support.

I know what i have to do.

Best Wishes!
Jacek Antek

[June7]

I have read that SQLServer Express 2008 has file size limit of 10GB, can't find any limit on MySQL.

[jaryszek]

Ok thank you very much June7 !

Best wishes,
Jacek