1

I have a piece of industrial equipment running SQL Server with a database named, let's say, "primary". The equipment creates on a daily basis a .bak file (of the full database) and it transfers it to a server running SQL Server. On the server, I need to have an ARCHIVE of "primary".

The equipment runs SQL Server Express. In a few year's time it could reach the 10Gb limit (perhaps earlier since, at this time, we don't know the amount of generated data). In that case, the equipment will use a clean database but I must keep all the data on the server.

My idea is to create a daily job to import only the new records from the .bak file, but I have no idea where to start from.

I have looked online for "importing .bak into SQL Server" but all I find is about the RESTORE command which I'm afraid doesn't work in my case.

Can anyone point me in the right direction?

UPDATE 1 - According to the warranty given to us by the manufacturer of the equipment, we can't interact in any way with the embedded SQL Server. The only way to access the data is via the .bak file.

0

1 Answer 1

1

Backup files (BAK files) can't be read to take only some records inside. You can only restore them. Then you can import from the restored database what you like.

The routine will be

  1. Copy the BAK from the device to your own server.

  2. Also copy the same BAK to a remote location so you can rebuild the ARCHIVE if your server fails.

  3. Restore BAK as a new database. Let's call this DAILY. It is OK to overwrite the same DAILY each time.

  4. Perform ETL from DAILY to ARCHIVE. This must identify when the equipment has passed 10GB and made a new DB. Otherwise it will look like a lot of rows have been deleted.

  5. Take a backup of ARCHIVE. Copy this to a remote location in case the server fails.

Note that when the equipment reaches 10GB and starts a new DB some data may be lost - the ones between the last backup and the end of the old DB. Talk to the manufacturer about precisely how they handle this (two BAK in one day?) and adjust these steps accordingly.

2
  • 2
    Thanks for the reply. So, If I understand well, I should have 2 databases on the Server. A permanent database with all my data and a temporary one created from the .bak file. After restoring the temporary database, I copy the new records from temporary to permanent. Is that correct?
    – Sig
    Commented Jan 28, 2021 at 13:42
  • Yes, if you want to procede with this ugly path of backup/restore/copy data. Better would be to build some kind of ETL or Replication between source and destination.
    – MBuschi
    Commented Jan 28, 2021 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.