Implications of full backup of running SQL Server database every 4th minute

Question

I have a customer that has set up a full backup of the database to an network path every 4:th minute on a heavy used database. I strongly recommend the customer to not to do that and instead do a transactional backup.

But what is the possibly bad things that can happened when doing a full backup in this way? Other than that the backup will never have correct data because of the database is in heavy use while doing the backup. And the workload of the server.

I have had a couple of sql-related problems (webservice<-sqlconnection->sql) with this customer and want to know technically if this backup scenario can be part of the problem?

Of course I will start to investigate this by stopping the backup while testing and look at eventlogs and so on. But Im curious about if anyone direct can say "NOOO, this will lock the X and throw the Y and is BAD" or something. ;)

Answer 1

Possibly you could run a second MSSQL server for redundancy? and have both servers in raid mirror?

or you could use something like Red Gate to backup your data every 5min. and its dead fast!!! and has lots of compression/encryption. We ran a backup over a database with 1mil records every 10min. No Problems!

You really have to ask your client why do you want to backup your database every 4min's?

Answer 2

As long as you are putting the data files in whatever MSSQL's equivalent of 'backup mode' is, the backup should be valid, but this seems like an incredibly heavy-impact way to do the backups. Even a 'full backup hourly, log-based backups continuously' would be a huge improvement.

Of course, it depends a lot on the size of the database, if it's a 5-10 Meg database, the overhead might be reversed, but that seems unlikely.

Answer 3

If it's a small database, and I'm guessing it is, it probably won't do any harm other than a little extra load, but if they need to make sure they don't lose data, they should be doing daily full backups and log backups every few minutes instead of full backups, and log shipping to a standby server, or set up mirroring.

Answer 4

I guess this is a small database, but once it outgrows its 4-minute window you can bring down the database: a second backup starts, further slowing down both processing and the earlier backup, waiting for a third backup to start...