Comprehensive data protection for all workloads
Post Reply
frank78
Lurker
Posts: 2
Liked: never
Joined: Aug 30, 2011 12:16 pm
Full Name: Asus Taiwan

Veeam and SQL Server Backup Integration

Post by frank78 »

My environment:
SQL Server 2008 standard Edition 32bit on Windows Server 2008 Standard Edition 32bit.
The server is hosted by one ESXi node.

Backup strategy:
Full backup every day at 09.00 PM
Differential backup every day every 2 hours between 05.10 AM until 09.10 PM
Log backup every day every 15 minutes between 05.05 AM until 08.05 PM
Theses backup files are then backed up on Tape during the night.

I know that the VMWare's administrator has implemented VEEAM Backup in order to take snapshots of this server but I have noticed something strange:

I have noticed a new messages in SQL Server errorlog exactly at the moment when Veeam takes the backup:

I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
I/O is frozen on database xxxxxx. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

and then

I/O was resumed on database master. No user action is required.
...
...

So there are no errors but at least something is strange to me.

I also noticed that the backup performed with Veeam writes information in msdb database.

i.e.

The table msdb.dbo.backupset contain records with this kind of informations:
is_snapshot = 1
is_copy_only = 1

The table msdb.dbo.backupmediafamily contain strange information in some fileds:
- for sql native backup I have a situation like this:

physical_device_name = C:\Folder\subfoder\db_xx_xxxx_FULL_20110809_213000.bak
device_type = 2
physical_block_size = 512

- for Veeam backup I have this one:
physical_device_name = {9D96FB8D-87FA-4B5B-9FC3-CEA7158FE10B}4
device_type = 7
physical_block_size = 1024


What is your suggestions for this kind of a situation?
My concern is about the fact that Veeam writes strange information in msdb database.

Could you please give me a feedback?

Thank you very much.

Best regards.

foggy
Veeam Software
Posts: 19434
Liked: 1764 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Veeam and SQL Server Backup Integration

Post by foggy »

What does exactly confuse you here? This is kind of absolutely normal activity during SQL Server backup. Messages in SQL Server errorlog are logged at the moment of taking a snapshot - as you have noted, no errors are logged, just notification. As for msdb records, these are most likely logged by VSS writer triggered during backup to quiesce SQL Server, not Veeam itself.

frank78
Lurker
Posts: 2
Liked: never
Joined: Aug 30, 2011 12:16 pm
Full Name: Asus Taiwan

Re: Veeam and SQL Server Backup Integration

Post by frank78 »

Foggy, thank you very much for your reply.
My concern is about the fact that in this way we backup our databases twice, one backup is made with sql server native tool at 9 pm and another one is taking with veeam (vss) sometime before, maybe at 8pm o 8.30pm, it's not always the same.
So we end up in having double information also in msdb database with a different form:

sql server native backup writes this information in msdb.dbo.backupmediafamily:
physical_device_name = C:\Folder\subfoder\db_xx_xxxx_FULL_AAAAMMDD_HHmmss.bak

veeam (vss) writes this information:
physical_device_name = {9D96FB8D-87FA-4B5B-9FC3-CEA7158FE10B}4 :cry:

I just notice that this is not a perfect design. :roll:

cell
Influencer
Posts: 10
Liked: 2 times
Joined: May 14, 2013 12:22 pm
Contact:

Re: Veeam and SQL Server Backup Integration

Post by cell »

I think i have a similar problem related to this.

We have some VMs that we backup with veeam but we also running a maintenanceplan from the sql that places the backup-files on the local disk.

The maintenanceplan takes a full backup every sunday and diff,logbackup during the day rest of the week. Veeam-backup runs once a day but after the veeam-backup maintenanceplan fails and complains about it cant find the last full backup.

We don´t want to run the veeam backup more than once a day and we need to local sql-dumps to import them to development-servers.

anyone have a suggestion how to solve this?

Best regards.

Vitaliy S.
Product Manager
Posts: 24243
Liked: 1859 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Veeam and SQL Server Backup Integration

Post by Vitaliy S. »

You may want to disable application-aware image processing for this VM completely, as you're backing up databases with native SQL Server tools anyway.

cell
Influencer
Posts: 10
Liked: 2 times
Joined: May 14, 2013 12:22 pm
Contact:

Re: Veeam and SQL Server Backup Integration

Post by cell »

Hi Vitaliy, i´ve disabled application-aware now, but i wonder how reliable the backup of the server is if i do this. Any chance windows not working properly after the restore etc? is quiece really an option for a sql-server?

Best regards.

dellock6
Veeam Software
Posts: 5926
Liked: 1743 times
Joined: Jul 26, 2009 3:39 pm
Full Name: Luca Dell'Oca
Location: Varese, Italy
Contact:

Re: Veeam and SQL Server Backup Integration

Post by dellock6 »

If you are already running a SQL maintenance plan, there is no need to do also VSS backups of it. VSS for windows does not do so much to require it, the operating system is able to recover from a crash-consistent backup without the need for VSS.
No, quiescence is not an option for SQL Server, it requires custom scripts to stop and restart the services, but it's useless since it already supports VSS libraries.

Luca.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software

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

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

Re: Veeam and SQL Server Backup Integration

Post by veremin »

Any chance windows not working properly after the restore etc?
As mentioned above, in case of you performing SQL maintenance backup, there is no strong demand to perform additional VSS-aware one.

In general, without AAIP the data in the backup would be in a “crash-consistent” state, it is the same as it would be after a system failure or power outage. However, you’re pretty safe in case of SQL server, since its databases (in contrast to other VSS aware applications) can be successfully recovered from such backup in 99% of cases.

Thanks.

Post Reply

Who is online

Users browsing this forum: Google [Bot] and 27 guests