Comprehensive data protection for all workloads
jgspitler
Novice
Posts: 4
Liked: never
Joined: Dec 13, 2012 4:39 pm
Full Name: Joe Spitler

SQL Backup Jobs

Post by jgspitler » Mar 06, 2013 9:36 pm

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
Veeam Software
Posts: 5734
Liked: 1625 times
Joined: Jul 26, 2009 3:39 pm
Full Name: Luca Dell'Oca
Location: Varese, Italy
Contact:

Re: SQL Backup Jobs

Post by dellock6 » Mar 06, 2013 11:55 pm

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.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software

@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2019
Veeam VMCE #1

veremin
Product Manager
Posts: 16901
Liked: 1439 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backup Jobs

Post by veremin » Mar 07, 2013 9:18 am

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
Novice
Posts: 4
Liked: never
Joined: Dec 13, 2012 4:39 pm
Full Name: Joe Spitler

Re: SQL Backup Jobs

Post by jgspitler » Mar 07, 2013 7:46 pm

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
Veeam Software
Posts: 5734
Liked: 1625 times
Joined: Jul 26, 2009 3:39 pm
Full Name: Luca Dell'Oca
Location: Varese, Italy
Contact:

Re: SQL Backup Jobs

Post by dellock6 » Mar 07, 2013 8:31 pm

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.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software

@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2019
Veeam VMCE #1

jgspitler
Novice
Posts: 4
Liked: never
Joined: Dec 13, 2012 4:39 pm
Full Name: Joe Spitler

Re: SQL Backup Jobs

Post by jgspitler » Mar 07, 2013 9:52 pm

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
Veeam Software
Posts: 5734
Liked: 1625 times
Joined: Jul 26, 2009 3:39 pm
Full Name: Luca Dell'Oca
Location: Varese, Italy
Contact:

Re: SQL Backup Jobs

Post by dellock6 » Mar 07, 2013 11:14 pm 1 person likes this post

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.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software

@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2019
Veeam VMCE #1

jgspitler
Novice
Posts: 4
Liked: never
Joined: Dec 13, 2012 4:39 pm
Full Name: Joe Spitler

Re: SQL Backup Jobs

Post by jgspitler » Mar 08, 2013 4:53 pm

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 7:57 am

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 9:37 am

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 01, 2013 10:17 am

Hi Arun,
zak2011 wrote: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.
zak2011 wrote: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.
zak2011 wrote: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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 10:55 am

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 01, 2013 10:59 am

zak2011 wrote: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.
zak2011 wrote: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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 11:09 am

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 01, 2013 11:19 am 1 person likes this post

Correct.

zak2011
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 11:40 am

Thanks Vitaliy for your help as always!

zak2011
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 12:32 pm

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 01, 2013 1:03 pm

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 2:29 pm

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 01, 2013 2:41 pm

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 01, 2013 2:52 pm

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 16, 2013 8:22 am

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 16, 2013 8:59 am

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
Veeam ProPartner
Posts: 23
Liked: 11 times
Joined: Oct 24, 2011 12:55 pm
Full Name: Brian Farrugia
Location: Malta, Europe
Contact:

Re: SQL Backup Jobs

Post by BriFar » Oct 16, 2013 9:02 am 1 person likes this post

zak2011 wrote: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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 16, 2013 9:18 am

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
Veeam ProPartner
Posts: 23
Liked: 11 times
Joined: Oct 24, 2011 12:55 pm
Full Name: Brian Farrugia
Location: Malta, Europe
Contact:

Re: SQL Backup Jobs

Post by BriFar » Oct 16, 2013 9:31 am

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 16, 2013 11:17 am

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
Expert
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Backup Jobs

Post by zak2011 » Oct 17, 2013 7:53 am 1 person likes this post

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
Influencer
Posts: 10
Liked: never
Joined: Oct 25, 2013 12:30 pm
Full Name: Marcin
Contact:

Re: SQL Backup Jobs

Post by djwheele » Oct 25, 2013 12:47 pm

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.
Product Manager
Posts: 23001
Liked: 1557 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Jobs

Post by Vitaliy S. » Oct 25, 2013 1:17 pm

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!

Post Reply

Who is online

Users browsing this forum: No registered users and 22 guests