I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database.
When I execute this T-SQL to show backup details
select * from msdb.dbo.backupset;
select * from msdb.dbo.backupmediafamily;
It shows that details of backup with manual backup details & virtual device backup details. I am also attaching the screenshot of backupset & backupmediafamily :
* After Edited*
I have check out from windows PowerShell , The SQLWriter service is running in window services. And my Windows Server 2012 R2 has installed in Domain environment. So, The virtual backup is running from SQLWriter service.
Why does it show most of the automatically backup taken in virtual device with NT AUTHORITY\SYSTEM
user name & by default it shows the backup with virtual device & their some virtual location. But my SQL Server database is not attached with any SAN device.
The database is on SQL Server 2012 Express. So, there is no any option of SQL Server Agent Automatic Backup. How it shows automatic backup in log files. Automatically it take the "Device_Type=7" which signifies virtual device location, as per MSDN https://msdn.microsoft.com/en-us/library/ms178018(v=sql.110).aspx & https://technet.microsoft.com/en-us/library/ms190284(v=sql.110).aspx if "Device_Type=7" i.e, Virtual device.
But here I would like to say that before migration of database, the database was on SQL Server 2012 Enterprise & it was setup with SQL Server Agent automatic backups. But this time using SQL Server 2012 Express.
Even automatically it is taking backup of "System databases" as well as "User Databases".
My question is that if any virtual device has not setup then why my SQL Server Express database is taking virtual backups. Even this database is my audit database. If this backup will going on continuation manner.
Is there any impact of my audit data?
Any suggestion would be appreciated?
Best Answer
This is because some tool or windows server native backup feature is taking a Snapshot backup of SQL Server. Such backups normally use
NT AUTHORITY\SYSTEM
andSQL Server VSS writer service
to connect to perform Snapshot backup.Please note since you said there is no third party backup scheduled I have guessed here that it might be windows native backup. If you want to find more about such backup start the profiler and capture the events when backup is running. Or you can schedule the profiler. I must say profiler can create load so be aware about that so be selective in events when configuring the profiler.
Workaround:
If you are very much certain that you don't need this backup, which IMO is useless as compared to native TSQL backup, you can
go ahead and disable SQL Server VSS writer service
. You can go to Services.msc and you would find this service there and then disable it.No, such backup is not going to have any affect on your data but yes it would be utilizing system resource when running.