SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logs show backup device Type = Virtual_Device when should be DISK


Logs show backup device Type = Virtual_Device when should be DISK

Author
Message
Doug S.
Doug S.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 333
We are using default SQL 2005 maintenance plans and SQL Agent for backups, there is no special backup devices or software. When trying to do point in time restore on a test DB got the error Cannot Open Backup Device {...} Operating system error 2 (The system cannot find the file specified).

Log records show that some backup are listed as Type=VIRTUAL_DEVICE and others are TYPE=DISK. There seems to be no pattern as to when this happens on full backups or tran logs.

I can see the files exist in the backup folder and the permissions on the folder have not changed. The account running the backup job has full access to the backup folders.

Can anyone explain when/why a virtual devices is created?



Doug
Doug S.
Doug S.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 333
It now appears that I have phantom backups generated from an unknown source. The backups that create problems with the virtual device are not executed by the SQL agent jobs that run 2 hours earlier.

Has anyone ever heard of using virtual devices from a trogan to execute backups and reroute data offsite? I'm interested in logging the account activity to determine where the connection originates from, and which security credientials are used to execute the backup command.



Doug
JamieX
JamieX
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1961 Visits: 437
Look for services that are running that you don't recognize ... or any that are related to backup software/vendors. Are you sure one of you sysadmins didn't install some backup software that also grabs DB backups?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)

Group: General Forum Members
Points: 124085 Visits: 18070
what is the result of the following query

select * from sys.backup_devices

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Doug S.
Doug S.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 333
Thanks Perry and Jamie,

I've got this solved somewhat. Here's the explanation. The backup agent for disk file backups triggered something in the Volumne Shadow Copy (VSS) service that generates backup commands. By disabling the SQL VSS service this fixes the problem. Here's more detail.

The VSS service can be used to perform live backups of databases or files while they are in use. When the backup agent executes VSS commands the SQL VSS agent executes full DB backup commands with the Virtual device. I noticed that a point in time restore failed because of the Full back that did not have an accessable disk file.

The virtual backups are useless to me and wreak havoc on restores. Our intent is to run normal DB and log backups and move the files offsite using the backup agent. Disabling the SQL VSS agent fixed the problem for SQL2005 DB's but not SQL 2000 DB's so now I must find a way to disable the SQL 2000 VSS agent.

In any event, I would not recommend using the VSS service unless there is a good way to work around the restore problem, or unless you need to make live DB backups using your backup agent.

BTW - there were no backup devices returned by the query.

Hopefully, this will help somboday else in the future.



Doug
sendrabin
sendrabin
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 19
For a SQL 2005 server, I see that there are log entries for database backups for both the type=DISK(according to job schedule) and type=VIRTUAL_DEVICE(at a fixed time each day). I stopped the 'SQL Server VSS Writer' service to see if this helps get rid of the VIRTUAL_DEVICE but to no avail.

For a SQL 2000 server, even when there are no backup jobs, there are log entries for database as well as log backups of type=VIRTUAL_DEVICE.Plus, there is no 'SQL Server VSS Writer' Service in 2000, if this is thought to be the one creating these virutal backups.

I just wanted to get rid of the backup log entries of type=VIRTUAL_DEVICE.
Any help or suggestion would be appreciated.

Rabin
Doug S.
Doug S.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 333
Rabin, I would look for another VSS writer service. It seems clear that SQL Server still thinks one is running. Try looking for a VSS service under another name.

Good luck, I know what a pain this is.

Doug



Doug
Jagdev
Jagdev
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 129
Does anybody got answer how to disable this?
erikegon
erikegon
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 34
It has helped me, after a few days of pulling my hair which rogue process was creating backups. Thanks for sharing!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search