Multiples full backup from MS SQL Server all day long

Question

I think this is not the best practice, but on a server I am administering, there is a PowerShell script doing full backups of a Microsoft SQL Server Express Edition instance every X minutes.

What issues could this cause to data being saved?

This database is only a few megabytes in size, but it is being used during the day.

Has anyone seen this?

Answer 1

It's a very expensive way of doing your backups. If they are taking full backups each x minutes that means the point of time they are taking the backup is the point they can restore to. For instance if they take fulls each 5 minutes they can restore ONLY in 5 minute blocks. It's most likely set up by someone who doesn't understand SQL backup process, but at least the recovery is easiest - pick the file closest to your desired restore point and restore it. Data loss will be up to x minutes.

A better practice would be one of:

1. If they really need to be able to restore to each 5 minutes and the database is in SIMPLE recovery, to take a daily FULL backup and then an incremental\differential each 5 minutes. This will depend on how much the data changes but the differential backup is most likely going to start very small and increase in size throughout the day. If a lot of data changes it's not going to be much more efficient than the current strategy. It's a slightly more complicated restore process consisting of the full backup then ONE differential closest to your restore time. You've saved space but still have up to x minutes dataloss. In almost every case the most efficient backup strategy will be.....
2. Set the database into FULL recovery, take a daily full backup and then take transaction log backups. These backups only record what has been written to the log and will usually be quite small. It's the most complicated retore process consisting of restoring your FULL backup and then EVERY log backup - but don't worry - Management Studio will put that chain together for you. It's the most space efficient(probably) and has the added advantage that you can restore to any time between your full backup and the most recent log backup you have - to the second.

Ultimately in all 3 backup scenario's your potential data loss is going to be up to the last backup time(so up to x minutes), but the two I cover above are going to save you a ton of space - particularly as the database grows. Transaction Log Backups have the additional advantage of recovery to any point in time, being smaller on disk in nearly every case, and in the event you lose your data file but not your log file, you can still take a final log backup of that and restore your data up to the point of failure. They are the correct solution in almost every case.