I have a Microsoft SQL DB running on Azure VM
I have daily snapshots of the VM and this is currently my only backup mechanism.
Is this considered best practice? Am I risking something here?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Before I tell you if you following best practice or no, I suggest you understand few terms which dictates what kind of backup plan is suitable for you. This will also enable you to decide if you are risking something or no.
There are three recovery models:
Full recovery model (the default and the most commonly used) All modifications in the database a fully logged The log will not clear until a transaction log backup is performed
Bulk-logged recovery model Some modifications (like an index rebuild or a bulk load) can be minimally logged, which reduces the amount of log records generated The log will not clear until a transaction log backup is performed, just like the full recovery model
Simple recovery model Some modifications can be minimally logged, just like the bulk-logged recovery model The log will not clear until a checkpoint occurs (usually automatically), and transaction log backups are not possible
There are three commonly used backup types in SQL Server:
Full database backup
Transaction log backup
Differential database backup
RTO stands for Recovery Time Objective.
In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again. RTO is often talked about in terms of the number of nines of desirable up time or accessibility for the data/database/system. For example, 5-nines means 99.999% up-time, and if that’s being measured 24 hours a day, 365 days a year, then that translates into just over 5 minutes of allowable downtime per year. That’s really hard to achieve. It’s easier to meet 4-nines (52.5 minutes per year of downtime) or 3-nines (8.75 hours per year of downtime).
When discussing the desired up time, you have to decide whether it’s being measured 24×365, or 9am-5pm during weekdays, or some other time period. You also need to decide whether the measured downtime includes scheduled downtime for maintenance/patching or not, as it’s much easier to meet a high number of nines if scheduled maintenance is permitted.
RPO stands for Recovery Point Objective.
In simple terms, you can think of RPO as a measure of how much data or work it’s acceptable to lose. It’s relatively easy to achieve very minimal or even zero data/work loss using backups, but depending on the amount of damage the database suffered when the disaster hit, recovering might take a lot of time. For instance, if an entire database is destroyed, depending on the architecture of the database and the backups that exist, it may take a significant amount of time to recover the database up to the point of the disaster. Most RPOs are defined as the the amount of time for which work may be lost. For example, an RPO might be that the database should be recoverable to a point within thirty minutes of the disaster occurring, which means that up to 30 minutes of work may be lost.
Once you understand the above concept then you need to make a backup strategy. Make sure your business counter-part is in agreement with your strategy, especially about how much data you can lose. Read the article I listed in reference about planning a recovery strategy.