SQL Backup Jobs

[djwheele]

Thank You Vitaliy

Is it a safe way?
What should I tell him if he ask me about the security and recovery plan?
If I disable this options will it cause for security, safety or recovery plan?

thanks in adventage

[Vitaliy S.]

Marcin,

If you disable application-aware image processing, then your VM image backup will be done in a crash-consistent way. Since you're already doing logs backup (for point-in-time restores, right?), then when the disaster strikes you will need to restore your SQL Server VM and then run the logs restore procedure with the same SQL native tool you're using now.

On top of that, do you have any special requirements to backup your SQL Server in the way you do it now? I mean using both native tool and Veeam backup?

Thanks!

[djwheele]

Thank You once more.

For this moment the situation is complicated.

In my location I am a DBA admin and I am responsible only for MSSQL server. I do not have access to Windows Server, to VM etc. For system (Windows servers, VM etc is responsible other team somewhere in the world)

I have planned my own backup way for my mssql server
one full backup at 9pm
diff backup for every 4 hours starting from 0:00
logs backup for every 15 minut starting from 0:00
all is done by mssql server agent through the maintenance plan.

I have seen the errors in mssql server logs and i asked the team from VM and server if the are doing something with vm.
They told me that "We have one Veeam backup scheduled at 8:00PM and Veeam replication for DR every 6h…4:00AM, 10:00AM, 4:00PM and 10:00PM"

Now I am trying to do all what I can to meet my boss needs for mssql server backup.
I do not know why the other team (servers and vm) has schedulled their veeam jobs as it it set.
I would like to do what I can to find a gold solution - good for me and them

Thank You for Your help

[Vitaliy S.]

If you're already doing your own backups, then ask VI backup guys to disable application-aware image processing and logs truncation for this VM and you should be good.

[djwheele]

Thank You again Vitaliy!
Could You clear me one more thing.

If we disable this application-aware image processing and logs truncation option for our VM machine with MSSQL server, and then the crash occur, we will have to recover our VM from the last full backup, from the last replica and ?? What about the transaction log? We will need to recover them from the last replica moment?

Could You describe me the recovery proces with disabled application-aware image processing and logs truncation option?

Thank You in advance
Marcin

[Vitaliy S.]

If we disable this application-aware image processing and logs truncation option for our VM machine with MSSQL server, and then the crash occur, we will have to recover our VM from the last full backup, from the last replica and ?? What about the transaction log? We will need to recover them from the last replica moment?

...and if you need to restore to any other time, you will need to restore transaction logs you're currently backing up with native tools.

Could You describe me the recovery proces with disabled application-aware image processing and logs truncation option?

Restore the entire VM and then if you need to go back to another point in time, you will need to use the backups you're doing now. Cannot comment on exact steps you should take, as I'm not SQL DBA and don't know what you're currently using to backup your SQL Server database.

On a side note, if you don't need to perform point-in-time restores, then I would suggest backing up your SQL Server VM with Veeam only (AAIP and log truncation enabled). In this case, all you have to do to restore this server is to spin up the VM from the backup and start working on it.

[foggy]

Marcin, Veeam B&R does not allow to perform point-in-time restores, so recovery process is actually the same in both cases - you will be able to restore the SQL database to the state is was backed up in. If you want to be able to apply logs and recover your databases to a specific point-in-time then you would need to use native SQL tools for that.