June 5, 2014 at 1:09 am
I have an sql 2012 express server.
Just checked the server logs and found that every day ~1PM every database backed up by the server automatically. These backups are available for restore, and the backups stored in some virtual device.
Here is the log entry for the master database:
06/04/2014 01:01:08,Backup,Unknown,Database backed up. Database: master<c/> creation date(time): 2014/05/19(09:08:20)<c/> pages dumped: 458<c/> first LSN: 216:342:73<c/> last LSN: 216:373:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{D54FF1D3-C286-4CC3-990E-5DEC5C44099B}5'}). This is an informational message only. No user action is required.
I have not scheduled any backup, have no maintenance plans, no sql server agent etc.
From where coming these backups? Is it safe to use these backups for recovery?
June 5, 2014 at 4:41 am
They could be coming from anywhere that can call the system with appropriate permissions. To know who made the call you'd need to set up extended events to monitor queries to capture the backup command. As to whether or not it can be used to recover the system, it sure sounds like a full backup, so yes. But to be sure, test it. Restore it to another instance of SQL Server to see if it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2014 at 4:52 am
Could be a 3rd-party generic backup app with a SQL plug-in.
June 5, 2014 at 5:37 am
DouglasH (6/5/2014)
Could be a 3rd-party generic backup app with a SQL plug-in.
+1. thats the type of message they tend to produce in the errorlog.
Beware, if they are not copy_only backups and you use differentials in your backup strategy it could be screwing up your recovery plans
---------------------------------------------------------------------
June 5, 2014 at 5:53 am
george sibbald (6/5/2014)
Beware, if they are not copy_only backups and you use differentials in your backup strategy it could be screwing up your recovery plans
+1 on this. I would check with your domain administrators and see if they have some corporate backup software that they're using to back up your databases. In fact, this is the exact situation I was in once. They were backing things up and the backups weren't available to me. Guess what happened when I was asked to do a point-in-time recovery once? It sure hit the fan on that one. Make sure you have controls of your backups. Someone will have permission to your instance with permission to run backups. I would start there first and then talk to your domain admins.
June 6, 2014 at 5:43 am
Thanks for pointing to the right direction. We are not using any 3rd party backup software.
The server is sbs 2011 (win2k8 R2 based), and we have a scheduled windows server backup at 1PM.
This is the built in ms backup of win2k8, our backup includes bare metal recovery, system state, system reserved, some folders and exchange, this backup points to an iscsi device.
It seems that our windows server backup enumerates all(!) sql server instances and makes backup of all databases. So these are legitimate and good auto backups.
This is a nice bonus of sbs, I have no problem with it. What is strange I see no option at all to control this behavior, and not found any documentation what is included in sbs backups.
June 6, 2014 at 6:01 am
Is the SQL Server VSS Writer service running? I think that makes backups at times that can appear arbitrary.
John
June 16, 2014 at 3:14 am
Checked and that service is running, but this should be the default on any installation. The backups are clearly made by the built in windows server backup, no arbitrary times in the logs, happens exactly when the scheduled server backup runs. Maybe the "bare metal recovery" option in the backup enumerates all sql server instances and backups everything.
June 16, 2014 at 8:10 am
stmarti (6/6/2014)
This is a nice bonus of sbs, I have no problem with it. What is strange I see no option at all to control this behavior, and not found any documentation what is included in sbs backups.
I'm looking at that as a bit of an oxymoron and, as a DBA, I'd personally have a problem with it because you have something outside of SQL Server controlling what goes on inside SQL Server and it's not something trivial... it affects the primary responsibility of the DBA and that is to safeguard the information and make sure that it can be restored at the proverbial drop of a hat.
The other problem that I'd have with it is that no one told you what they're doing. You just can't have that kind of communication gap between the Windows Team and the DBA(s) and expect continued success. For example, what is their written plan for restoring a database to a "Point-in-Time"? Where are the log file backups associated with their latest full backup? How many days of backup are available? What is the growth plan for the storage involved?
The next question would be, do you have your own backup system running to begin with? If yes (No one takes care of SQL Server data like a good DBA), then I'd have them turn those externally driven backups off. If no (and apologies for being extremely blunt), then the DBA(s) need to get off the can and set a proper backup system up.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2014 at 8:30 am
I strongly agree with Jeff there.
June 16, 2014 at 8:30 am
And are there differential backups? If so, this stuff could be breaking your backup chain, which could lead to all sorts of fun when you try to recover.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2014 at 8:38 am
Yeah, I have to agree as well. The DBA must be able to restore from backup. If you/they don't know where the backups are, that would put a severe damper on them being restored when needed. Even if the DBAs have their own, the Windows team running backups to an undisclosed location take you out at the knees if you have to do a point-in-time recovery. The logs only apply to the last full backup, so you need the full. If the communication between the DBA and Windows teams doesn't exist, it's probably going to take some work to get it going, but this is too important to just let go.
Good luck with it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply