2

We have some databases on our server with 'Simple' recovery mode. We are using a third party application for the whole server backup every 30 minutes. We are also taking SQL server database backups twice a day (plan).

My question is

  • When we take SQL database backup with Simple recovery mode and restore it on a test server, is it correct that we have a blank log file on the test server but the latest database file?
  • In our current backup scenario, what is the benefit of Full recovery mode as we already have replication every 30 minutes and our RPO is agreed as 1 hour for every client?
  • I am not a DB admin and probably that is why I have never come across a situation where I had to use SQL server database log file. Whenever anything happened I have always restored the database to the last taken backup which is around 30 minutes old. Can anyone highlight if I need to improve on this point?

I'm just a learner and any guidance / advice is much appreciated.

2

When we take SQL database backup with Simple recovery mode and restore it on a test server, is it correct that we have a blank log file on the test server but the latest database file?

Yes, that's normal. When using the Simple recovery model, the transaction log isn't used for recovering the database (for instance, during a restore). It's essentially just a performance optimization. Data changes are written efficiently to the log first, and modified data pages are written back to the data files periodically during a process called a CHECKPOINT.

In our current backup scenario, what is the benefit of Full recovery mode as we already have replication every 30 minutes and our RPO is agreed as 1 hour for every client?

If that meets your RPO needs, then log backups are really unnecessary for your situation.

However, it's important to make sure that these every-30-minute, full server backups are "SQL Server aware." See Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment

SQL Server supports virtualization-aware backup solutions that use VSS (volume snapshots). For example, SQL Server supports Hyper-V backup.

Virtual machine snapshots that do not use VSS volume snapshots are not supported by SQL Server. Any snapshot technology that does a behind-the-scenes save of a VMs point-in-time memory, disk, and device state without interacting with applications on the guest using VSS may leave SQL Server in an inconsistent state.

They should be freezing I/O, otherwise the backed up database files could be in an inconsistent (corrupted) state. Verify this with your vendor or their documentation. A SQL Server backup triggers the CHECKPOINT process mentioned above, which means all data that's been modified in memory gets written back to disk before the backup.

One advantage of log backups is that you can restore to a specific point in time. This can help when recovering from "oops" DBA actions (deleting or updating data accidentally - for instance, by forgetting a WHERE clause).

Can anyone highlight if I need to improve on this point?

The fact that you're thinking about RPO at all probably puts you ahead of a lot of folks in your situation 😀 Double check that the VM backups are compatible with SQL Server, but otherwise it sounds like you're on the right track!

| improve this answer | |
  • Thank you so much @Josh for the useful and to the point answer. – ady Jun 28 at 6:32
  • @ady You're very welcome! – Josh Darnell Jun 29 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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