SQL Backup Jobs

[jgspitler]

I am a SQL Server consultant. I know little about Veeam. I have a client that uses Veeam. I have noticed that the Veeam backups actually record when a database is backed up in the SQL log file just the a regular SQL back-up job does when scheduled through the SQL job agent. In place of the path and file name of the BAK file file there is a GUID.

My client has SQL jobs scheduled for nightly full backups and differential backups every four hours during the day. They also have Veeam backups scheduled. Here's the pattern:

12:00 AM - SQL Full
4:00 AM- SQL Diff
5:00 AM - Veeam full backup. In the sql log I see a full backup at 5 AM.
8:00 AM SQL diff.
... diffs every 4 hours.

At 9 AM when I looked at the backup chain in SQL it is indeed indicated that a full backup was taken at 5 AM (Veeam backup) with the SQL diff having been taken at 8 AM.

This is all good and well if indeed at 5 AM Veeam did in fact run a "SQL Server" backup and that I do have BAK file I can use in combined the differential that was taken at 8 AM.

Is Veeam running a true blue SQL backup and producing a BAK file that can then restore with using a SQL Restore command?

Thanks.

Joe

[dellock6]

Hi Joe, no it does not. What happens is Veeam invokes VSS libraries to freeze the db, takes a copy of the entire VM after vCenter has issued a VM snapshot, and if the backup job )of the entire VM) is successful, it truncates the logs. So yes is correct technically is a full DB backup, but you do not have the bak available, when you need a restore you recover directly the mdf/ldf files from the backup.
If you prefer, you can disable log truncation and let only SQL backups to do truncation, but Veeam will register anyway the SQL backup, unless you disable VSS processing in the Veeam backup job.

Luca.

[veremin]

In other words, to create transactionally consistent backup of a VM running VSS-aware applications (such as Active Directory, Microsoft SQL, Microsoft Exchange, Sharepoint) without powering them off VB&R puts into use its proprietary mechanism Application Aware Image processing.

Not only does it ensure successful VM recovery, as well as proper recovery of all applications installed on the VM without any data loss, but also it notifies applications about them being backed up. That was the reason why backup performed by VB&R was considered as normal one from SQL-server perspective.

Hope this helps.
Thanks.

[jgspitler]

So the differential backups that happened after the Veeam backup are useless. They have to be because as far as SQL is concerned it thinks there is a full backup occurring at the time Veeam performed it's backup. It this case the differentials after 5 AM are useless.

v.Eremin. I'm fuzzy on your comment..

"Not only does it ensure successful VM recovery, as well as proper recovery of all applications installed on the VM without any data loss"

Again, according to the sql logs it looks like there is a backup taken at 5 AM. If I have a hard drive failure at 6 PM but I can only recover from the 5 AM backup.

Thanks.

Joe

[dellock6]

Joe, put it simply, if you want to rely on the SQL maintenance plan because it better fits your requirements, you can simply disable VSS backups in Veeam and take only a crash-consistent backup of the VM, and use SQL to do the application backups.

Luca.

[jgspitler]

Luca, thanks for your response.

All I am trying to do is get educated and to give my client the correct answer. My client has implemented both types of backups. They have scheduled SQL differential backups. My point to them is that currently the differential backups that are taken after 5 AM are useless because Veeam backup that is timestamped 5 AM because Veeam does create a BAK file. I think this statement is correct, Am I mistaken? If the Veeam backup precedes a SQL Full, then followed by SQL differentials, now were in business.

[dellock6]

Well, Veeam has done a backup of SQL, and is usable for restores! If you do a file level restore in Veeam, you can browse into the SQLData folder, and the MDF file you will find there has been saved with VSS, so the database was not open and is not corrupted.
What you probably are missing is that in this case, the SQL backup is in another position thant your scheduled SQL backup, but is there and usable! If you prefer, as I said, to manage everything with SQL itself, you can disable VSS processing, or convert the 8 AM SQL backup to be another full and not a diff.

Luca.

[jgspitler]

Luca, thank you! I've been trying get this answered for quite some time. It didn't make any sense to me that Veeam would report a valid backup to the SQL log, and update the MSDB database with a valid SQL back up if it was really creating a valid SQL backup (BAK file). I'm fine with my client using but but I am going to suggest that the Veeam backup happen first, followed by a full SQL then the differentials. That way they don't cross and it doesn't get confusing.

[zak2011]

We had some issues with one of the SQL databases, this morning and the transaction logs for this particular database was filling up quickly. Veeam backups up the sql server every four hours every day. However the transaction logs keeps filling up very quickly as there are too many transactions with this database.
The recovery model set on this database is Full. Our Web developer suggests to change the recovery model for the database to Simple.
If the database recovery model to Simple, then point in time recovery wont be possible with Veeam, is that correct?
However their requirement now is not to have a point in time recovery, but to be able to go to previous days backup or two backups before.
If this is the case, shouldnt i be taking a full backup of this sql server from Veeam every day? Please correct me if I understood incorrectly.

Thanks!

[zak2011]

What i meant..was that our webteam says now they need to use simple recovery model for this db which grows fast, and they dont require point in time recovery, but recovery to the last full backup for this db.
Is there a way then a way to set specific retensions for just that db through Veeam or should i take a full backup of the complete sql server every day as a seperate job?

Thanks!

[Vitaliy S.]

Hi Arun,

If the database recovery model to Simple, then point in time recovery wont be possible with Veeam, is that correct?

Yes, and with all other backup tools you're using to protect this SQL Server database.

However their requirement now is not to have a point in time recovery, but to be able to go to previous days backup or two backups before.
If this is the case, shouldnt i be taking a full backup of this sql server from Veeam every day? Please correct me if I understood incorrectly.

Yes, you have understood this correctly, you can keep running your backup jobs once a day, that would give you a desired RPO.

What i meant..was that our webteam says now they need to use simple recovery model for this db which grows fast, and they dont require point in time recovery, but recovery to the last full backup for this db.
Is there a way then a way to set specific retensions for just that db through Veeam or should i take a full backup of the complete sql server every day as a seperate job?

No need to create a separate job for that. Why not to continue running your existing job? BTW, does your team want "full" backup every day or just a restore point for each day?

Thanks!

[zak2011]

Thanks Vitaliy.

"Why not to continue running your existing job? BTW, does your team want "full" backup every day or just a restore point for each day?"

They dont need point in time recovery, however they said they may need to go the backup to the previous two or three days ( which i beleive would be Full, since they want to use Simple Recovery Model). In that case, what would be the best setting?
All my sql servers are grouped in one folder with the job settings to backup them all up every four hours. Then wouldnt I require to set a seperate job for this sql server alone?

Thanks

[Vitaliy S.]

hey dont need point in time recovery, however they said they may need to go backup to the previous two or three days. In that case, what would be the best setting?

The best setting would be to run your backup job once a day either in reversed or forward incremental backup mode.

All my sql servers are grouped in one folder with the job settings to backup them all up every four hous. Then wouldnt I require to set a seperate job for this sql server alone?

Yes, you can exclude this server from the job (using the Add VMs step of the wizard) and create a separate job that you would run on daily basis.

[zak2011]

That means create a seperate job for this server with Reversed incrementals ( as it goes to normal disk storage) with perhaps three or four restore points
so if they require a backup ( not point in time) from the last day or the day before, it can be restored.

[Vitaliy S.]

Correct.

[zak2011]

Thanks Vitaliy for your help as always!

[zak2011]

However one more thing! This requirement is only for one database on this SQL VM. If I move it out and create a seperate job for this with three of four restore points, what about the other databases in the same instance having Full recovery model?

[Vitaliy S.]

In this case you should definitely use application-aware image processing with logs truncation option enabled. For the database using simple recovery mode this will not have any effect.

[zak2011]

Thanks Vitaliy. Just to be really clear on this for me and to explain to the Web team..I must do this..
1) Create a separate job for this SQL VM with Reverse Incremental mode ( with require restore points).
2) Enable application aware image processing , so that all databases with Full Recovery mode can have log truncation and restore to point in time possible through Veeam depending on number of restore points.
3) The special database with Simple recovery mode however will not have any effect and there will be no log truncation from Veeam as log truncation will be done from SQL itself. Incase of restore for this special database, only the Full backup will be available from Veeam.
4) Selecting the number of restore points ( eg: 4) needed for the job will have following effect for the retension of all databases on this instance.
a)For special database when restoring only one Full backup with three restore points will be available.
b) For other databases with Full Recovery Model, point in time recovery with 4 restore points depending on how often the job is run

[Vitaliy S.]

1. Yes.
2. Not exactly. Point-in-time restores are possible if you follow the procedure mentioned in this thread.
3. Yes.
4. Not really. You will have 4 restore points (full backups) available for all databases (no matter what recovery mode is selected).

[zak2011]

Thanks Vitaliy! Sorry about the last two statements I had written...I just realized it was a mistake. 4 restore irrespective of the recovery model.

[zak2011]

One of our primary databases comes up with an error 'The transaction log for database 'abcd' is full due to 'LOG_BACKUP' and no one is able to log on.
All our SQL servers are backed up every four hours. When I checked the Veeam backup statistics, it says its completed the job with Warnings. The following message

Code: Select all

10/16/2013 8:50:35 AM :: Unable to release guest. Details: Unfreeze error: [Backup job failed.
Cannot create a shadow copy of the volumes containing writer's data.
A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{5ee38a4d-fac7-4e5b-808f-9ef2c5ebb1d9}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].]

There are just 8 databases on this instance. As the SQL writer failed, Veeam was unsucessful in truncating the logs.
Because of this, I am being asked to consider the option of backing up SQL using SQL management studio and do only image backups using Veeam. Any particular reasons as to why the SQL writer failed leading to the logs getting full?

Thanks

[Vitaliy S.]

Hi Arun,

I think there should be more information in the Windows Event log regarding VSS writer state. Can you please check it?

Thanks!

[BriFar]

One of our primary databases comes up with an error 'The transaction log for database 'abcd' is full due to 'LOG_BACKUP' and no one is able to log on.
All our SQL servers are backed up every four hours. When I checked the Veeam backup statistics, it says its completed the job with Warnings. The following message

Code: Select all

10/16/2013 8:50:35 AM :: Unable to release guest. Details: Unfreeze error: [Backup job failed.
Cannot create a shadow copy of the volumes containing writer's data.
A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{5ee38a4d-fac7-4e5b-808f-9ef2c5ebb1d9}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].]

There are just 8 databases on this instance. As the SQL writer failed, Veeam was unsucessful in truncating the logs.
Because of this, I am being asked to consider the option of backing up SQL using SQL management studio and do only image backups using Veeam. Any particular reasons as to why the SQL writer failed leading to the logs getting full?

Thanks

Hi,
in cmd type vssadmin list writers and see if you have writers that are in the failed state.
Try restarting the Volume Shadow Copy service and re-run the command.
This should sort out the issue. Sometimes we needed to restart SQL service or the server.

Regards

[zak2011]

Hello Brian,

Thanks for your inputs. Restarting the SQL writer service or rebooting the SQL server may resolve the issue. However, i would really like to know why it happens sometimes on this sql server.
Previouly foggy has shared an excellent blog related to SQL VSS writer issues
http://sql-blogs.com/2012/01/17/trouble ... er-issues/
Unfortuanetly when i go to the application logs, i see only today's logs.

[BriFar]

Hi zak,
that is indeed a great blog post. May I suggest increasing the log file size maybe it will help in catching the actual cause?
I do not know the windows version you have but for Windows Server 2008 R2 and Windows Server 2012 you can find the instructions to increase it here:
http://technet.microsoft.com/en-us/libr ... 48849.aspx
hope it helps you out

[zak2011]

Hi Brian,
Thanks for that. I have increased the log size.
After having restarted the SQL VSS writer service, the writer state is stable but the log size of the database continues to keep growing rapidly. Veeam is scheduled to backup all databases every four hours and all the databases have full recovey model.
However I would really like to know why the SQL writer failed and also if something needs to be done from the database point of view to control this rapid log growth rate.

[zak2011]

After investigating further with the web team it was found there were some inconsistent files within the database that was causing the db to grow large after Veeam had truncated the logs. After they removed the problematic files within the database the log size seems to be under control.
Also the SQL VSS writer service is working.
Thanks!

[djwheele]

Hi,
I have a similar problem.
I have a full backup at 9PM every days.
Also I have a DIFF backup for every 4 hours starting at 00:00.

I have also a Veeam backup scheduled at 8:00PM and Veeam replication for DR every 6h…4:00AM, 10:00AM, 4:00PM and 10:00PM.

As You know only one DIFF backup is ok - the one at 00:00. The rest is failed becouse a have a message:
"Cannot perform a differential backup for database "DBA_Admin", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. Msg 3013, Level 16, State 1, Server SQLSERVER, Line 1 BACKUP DATABASE is terminating abnormally. Process Exit Code 1. The step failed."

Could You tell me what to do to solve this problem? My Boss would like to have a veeam backup and also sql full and diff backups.
Is there any possibility to do that ?

Regards
Marcin

[Vitaliy S.]

You need to disable log truncation/application aware image processing for this VM, since you're already backing up logs with built-in SQL tools. Thanks!