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!