Veeam and SQL Server Backup Integration

[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]

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]

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

I just notice that this is not a perfect design.

[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.]

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]

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]

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.

[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.