2

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 :

enter image description here

enter image description here

* After Edited*

enter image description here

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?

Improve this question
2
  • Is there a Windows OS level backup? Those usually use Sql Server's VSS provider when backing up and can be seen as backup to virtual device in Sql Server's logs. So ask the server / backup admin team if such a job exists and check if the schedules match your observations.
    – vonPryz
    Commented Jan 17, 2016 at 13:27
  • @Shanky, Sorry for late response. You are right . The virtual backup is running from SQLWriter Service. I have edited the question & also some write statement there.I am accepting as a answer.And It should be for +1
    – Md Haidar Ali Khan
    Commented Jan 20, 2016 at 7:50

1 Answer 1

Reset to default
3

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

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 and SQL 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.

If this backup will going on continuation manner. Is there any impact of my Audit Data?

No, such backup is not going to have any affect on your data but yes it would be utilizing system resource when running.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.