June 12, 2014 at 4:20 am
Hi - I have backup maintenance plans setup to run through the SQL Agent. I've noticed in the SQL logs that there are entries on four occasions, backing up to a Virtual_Device which I haven't setup.
There are a sequence of messages leading up to the backups:
(1) I/O is frozen on database
(2) I/O was resumed on database
(3) Database backed up, device information: (FILE=1, TYPE=VIRTUAL_DEVICE
I've done some research and it appears that these are generated through the SQL Writer Service which is a backup device interface. As well as our Maintenance plans which backup to the local drive, we use Tivoli software which stores these backups onto tape. I can only assume that the Tivoli software is the cause of these messages. Does this sound like a feasible explanation?
Many thanks
Dax
June 12, 2014 at 9:42 am
Your systems/SAN/infrastructure whatever you call them team may well have decided to take your SQL backups for you. They sometimes do this :-D.
Under these circumstances you need to talk to them about what sort of backups they're taking (and are they blithely truncating the log without your knowledge?), and where the files will be if you ever need to do a restore. Because it could be that in the event of a disaster you find yourself having to collect your files together from more than one location before you can start on your restore, which is an extra layer of hassle you don't need when you are already a bit stressed.
I prefer to look after my SQL backups myself, and then have the SAN admins back up my backup files to tape afterwards.
June 12, 2014 at 9:42 am
[dup]
June 12, 2014 at 10:51 am
Hi - Many thanks for that, I will speak with our Tech Team to establish what's happening.
June 12, 2014 at 10:39 pm
Beatrix Kiddo (6/12/2014)
Your systems/SAN/infrastructure whatever you call them team may well have decided to take your SQL backups for you. They sometimes do this :-D.Under these circumstances you need to talk to them about what sort of backups they're taking (and are they blithely truncating the log without your knowledge?), and where the files will be if you ever need to do a restore. Because it could be that in the event of a disaster you find yourself having to collect your files together from more than one location before you can start on your restore, which is an extra layer of hassle you don't need when you are already a bit stressed.
I prefer to look after my SQL backups myself, and then have the SAN admins back up my backup files to tape afterwards.
To add further if the backup has been schedule and taken care by Storage team yes we do see this kind of messages in sql log which I have also notice. But in case of disaster DBA is not in a position to restore the backup as storage might have different backup.
Hence, set SQL backup form DBA perspective elase make clear if storage is taking care of DBA backup; DBA is not responsible for restoration of the database and restore will be taken care by storage team. One more option ask management to have training on backup/restore database via storage.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 13, 2014 at 12:40 pm
If your SQL Server is on VM/ESX, the 'frozen/thawed' messages could be from what they call a 'VMDK'.
On the other hand it could also be due to having backup 'agent' software installed as well.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 13, 2014 at 1:10 pm
Also check the values of msdb.dbo.backupset.is_snapshot and/or msdb.dbo.backupset.is_copy_only for your database. Could be your VM backup software is taking a sql db snapshot as part of the server backup. Is_snapshot = 1 or is_copy_only = 1 will not interfere with your backup chain.
You can prove it to yourself by testing restores.
June 13, 2014 at 2:38 pm
This IO frozen messages because of Virtual server backups. When you take a complete backup of virtual server; for consistency purpose backup software freeze the IO for few seconds. This is reason you are getting informational messages on your SQL Server log.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply