No tags yet.
No Backups, No Logs, No Dice...
It's becoming less surprising, but no less scary when I find shops not taking regular database backups. This happens quite often in situations where there is no dedicated DBA. Meaning someone else just kind of took over those responsibilities ("accidental DBA", if you will).
Why can't I just include the .mdf and .ldf files in a system wide backup?
Some of the common explanations I get is that "Well we run a file system backup, so the database files are backed up". Unfortunately this will not provide sufficient results. SQL server keeps a lock on any attached database. Running some type of traditional file system backup will NOT work. If you don't believe me, do a test restore....
We take snapshots of our Virtual Machines.
This is great but still not as useful as database level backups. Listen, virtualization is amazing and has tons of benefits. But here's a one argument why this isn't the best option to cover your databases:
Let's say that there is some accidental data error on a particular table in particular database and you need to restore it. The VM snapshot will revert the entire machine, meaning if you have multiple databases on this SQL instance, it will revert them too?
That doesnt' sound very efficient and I'm sure the business side and application side(s) will not be happy.
With a DB level backup already in place, you can restore just that database (elsewhere or with a different name), pull the relevant data from the table and restore in production.
Let's not forget point in time restore.
Taking regular log backups is also essential if the business defined an RPO accordingly. Meaning, you can take Transaction Log backups as often as you need depending on how the business has defined it's RPO. Let's say 15mins of data loss is "acceptable". With log backups being taken every 15mins, you can then restore a database to a point in time.
Test/Verify your backups!
Always test and verify your backups on a regular basis. You should have an instance in place just for this purpose. Every month choose a critical database and restore it from backup. Run DBCC CheckDB and any other application side tests to ensure that the data is consistent.
Keep a log of this and show to your management every quarter that this is being done. It will give everyone piece of mind.
When the client just doesn't get it.
Usually when clients bring me in and I see no database backups and explain to them the importance of it. They're typically quite responsive and we strategize a plan almost immediately to remedy this issue.
On the rare occasion, I'll have a client that just doesn't "get it" or is too stubborn to put something in place in regards to DB level backups. I've come to a place now that if a client refuses to understand the basic importance of DB level backups, I turn down any further work for them.
No matter how much I get in writing that any data loss will not be my responsibility, as a DBA I cannot in good conscience proceed with any type of project that doesn't have backup fundamentals down.
Sorry, but no dice.