Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Restore Data from Backup Database

Discussion in 'Information Technology' started by Alastair MacFarlane, Oct 30, 2005.

  1. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Dear all,

    I have 2 Job logging databases, one live and one backup. The Backup is
    nearly a copy of the live database but contains 'posted' Jobs (i.e. jobs
    appended to the backup database and deleted from the live.) The client now
    states that they sometimes wish to reinstate these jobs (sometimes years
    after they have been added to the backup database).

    I can use a mixture of Append and delete queries to move the data back to
    the 'Live' database. The problem is that the Primary Key of the table on the
    one side will change so that Referential Integrity will not allow me to add
    a record in the many side without an associated record in the one side. As
    far as I can see the only way I can achieve my goal is to find out what the
    newly created PK is and alter the records on the backup database prior to
    appending them to the live database. This is messy and I wonder if I am
    missing something really simple here.

    Thanks again for the group's help.

    Alastair MacFarlane
     
  2. Loading...

    Similar Threads Forum Date
    Heritage Private Firm Engaged to Restore Interiors of Harmandar Sahib History of Sikhism Nov 16, 2013
    UK Rare Set of Sikh Armour to be Restored Breaking News Aug 16, 2013
    SciTech Eternal Sunshine of the Bionic Mind: Prosthesis Could Restore Memory Breaking News Jun 24, 2013
    An American Turned Sikh to Restore Ancient Gurdwaras Sikh Gurdwaras Jan 10, 2013
    UK Never-displayed rare Sikh chain armour to be restored Breaking News Sep 29, 2010

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Alastair MacFarlane wrote:
    > Dear all,
    >
    > I have 2 Job logging databases, one live and one backup. The Backup is
    > nearly a copy of the live database but contains 'posted' Jobs (i.e.
    > jobs appended to the backup database and deleted from the live.) The
    > client now states that they sometimes wish to reinstate these jobs
    > (sometimes years after they have been added to the backup database).
    >
    > I can use a mixture of Append and delete queries to move the data
    > back to the 'Live' database. The problem is that the Primary Key of
    > the table on the one side will change so that Referential Integrity
    > will not allow me to add a record in the many side without an
    > associated record in the one side. As far as I can see the only way I
    > can achieve my goal is to find out what the newly created PK is and
    > alter the records on the backup database prior to appending them to
    > the live database. This is messy and I wonder if I am missing
    > something really simple here.
    > Thanks again for the group's help.
    >
    > Alastair MacFarlane


    I am not sure of the best way to solve your problem, but I do have a
    suggestion to prevent it in the future.

    Don't delete the old data, rather mark it inactive and use a filter to
    eliminate it from queries, forms etc. Then just use that check box (or any
    binary type) to indicate it is inactive.

    Unless you have huge amounts of data you don't need to delete data that
    might under even unexpected situations may be needed.

    Then use your backup database as just that a total back up of all your
    data.

    --
    Joseph Meehan

    Dia duit
     
  4. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Dear all,

    Is there any way in access that I can switch of the PrimaryKey constraint
    Append data from an action query and start the PrimaryKey constraint again.

    If I have a table that has:

    ID (PK) - Autonumber
    Field1

    with values of :

    ID: 1; Field1: A
    ID: 2; Field1: B
    ID: 4; Field1: C

    How can insert an ID of 3 and any filed value into the Field1 column? If you
    add another record you will get the next autonumber (5 in this case) but I
    want it to be 3. Can this be achieved? You can remove the PrimaryKey
    constraint, add the data but you then can't save the table.

    Thanks again for any thoughts.

    Alastair

    "Alastair MacFarlane" <anonymous@microsoft.com> wrote in message
    news:%23PPoQ2H3FHA.1420@TK2MSFTNGP09.phx.gbl...
    > Dear all,
    >
    > I have 2 Job logging databases, one live and one backup. The Backup is
    > nearly a copy of the live database but contains 'posted' Jobs (i.e. jobs
    > appended to the backup database and deleted from the live.) The client now
    > states that they sometimes wish to reinstate these jobs (sometimes years
    > after they have been added to the backup database).
    >
    > I can use a mixture of Append and delete queries to move the data back to
    > the 'Live' database. The problem is that the Primary Key of the table on
    > the one side will change so that Referential Integrity will not allow me
    > to add a record in the many side without an associated record in the one
    > side. As far as I can see the only way I can achieve my goal is to find
    > out what the newly created PK is and alter the records on the backup
    > database prior to appending them to the live database. This is messy and I
    > wonder if I am missing something really simple here.
    >
    > Thanks again for the group's help.
    >
    > Alastair MacFarlane
    >
     
  5. Alastair MacFarlane

    Alastair MacFarlane
    Expand Collapse
    Guest

    Joseph,

    Thanks for your comments. The design approach is necessitated by volume of
    data, the cost implications of going to SQL, and the speed of the system
    when large recordsets are being processed.

    I may have to go with this approach if the preferred method is too
    cumbersome to programme.

    Thanks again.

    Alastair MacFarlane


    "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    news:pgK8f.161284$lI5.29220@tornado.ohiordc.rr.com...
    > Alastair MacFarlane wrote:
    >> Dear all,
    >>
    >> I have 2 Job logging databases, one live and one backup. The Backup is
    >> nearly a copy of the live database but contains 'posted' Jobs (i.e.
    >> jobs appended to the backup database and deleted from the live.) The
    >> client now states that they sometimes wish to reinstate these jobs
    >> (sometimes years after they have been added to the backup database).
    >>
    >> I can use a mixture of Append and delete queries to move the data
    >> back to the 'Live' database. The problem is that the Primary Key of
    >> the table on the one side will change so that Referential Integrity
    >> will not allow me to add a record in the many side without an
    >> associated record in the one side. As far as I can see the only way I
    >> can achieve my goal is to find out what the newly created PK is and
    >> alter the records on the backup database prior to appending them to
    >> the live database. This is messy and I wonder if I am missing
    >> something really simple here.
    >> Thanks again for the group's help.
    >>
    >> Alastair MacFarlane

    >
    > I am not sure of the best way to solve your problem, but I do have a
    > suggestion to prevent it in the future.
    >
    > Don't delete the old data, rather mark it inactive and use a filter to
    > eliminate it from queries, forms etc. Then just use that check box (or
    > any binary type) to indicate it is inactive.
    >
    > Unless you have huge amounts of data you don't need to delete data that
    > might under even unexpected situations may be needed.
    >
    > Then use your backup database as just that a total back up of all your
    > data.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
     

Share This Page