SQL backup every minute?

[Sangrey]

Hi,

So a new product we're installing has a rather sensitive but small (<100MB) SQL database that has the requirement of being backed up as often as possible, preferebly every minute.
Is there a way I can do this smoothly with Veeam, or is there another way that might be more suitable for this scenario?

Thanks for any help on the matter.

//Sangrey

[PTide]

Hi,

In this case you should check transaction logs backup, as it provides you with the best possible SQL database RPO.

Thanks

[foggy]

Backup every minute is not possible though. 5 minutes is the lowest possible value.

[nmdange]

What is the purpose of backing up every minute? Veeam can to point-in-time recovery so you don't have to backup every minute to be able to restore to a particular point in time. If the issue is that you can't lose any data in the event of a system failure, then you should really be looking at two SQL Servers with a SQL AlwaysOn Availability Group in Synchronous mode.

[Sangrey]

It's a rather sensitive system containing positional data of items with enough movement every minute that losing more than 1-2 minutes of data in case of system failure means we'd have to manually go through and recreate it all, since we can't confirm that the locations of the items is correct compared to the restored data.

But yeah, it's a decent step from the usual 1-2 restorepoints a day that the company usually goes with.

Thanks for the input nonetheless.

//Sangrey

[Sangrey]

In this case you should check transaction logs backup, as it provides you with the best possible SQL database RPO.

Hi,

Thanks, I'll check it out.

//Sangrey

[PTide]

It's a rather sensitive system containing positional data of items with enough movement every minute that losing more than 1-2 minutes of data in case of system failure means we'd have to manually go through and recreate it all, since we can't confirm that the locations of the items is correct compared to the restored data.

And now I got very curious about what's the product called and what exactly it does, would you tell us more please?

Thank you

[Sangrey]

Hi,

It's an AutoStore system, the database in question handles robot and item locations, and things can move around quite a bit in a couple of minutes. Thus the requirements for near constant backup in case of something going haywire.

//Sangrey

[gurneetech]

My approach would be to use something like Ola Hallengren SQL backup scripts, database recovery model 'Full' and schedule the full and diff (transaction log) jobs via the SQL Agent. You can schedule the agent down to every minute.

At that point, I would rely on veeam to back up the resulting files to get them off box, etc. Your restore method is far from one-touch, but with some scripting it shouldn't be too bad.

[Sangrey]

Thanks for the input, it's much appreciated.

//Sangrey