Backing Up SQL VM Server Without Client Agent
Hi,
Just looking at an issue that is confusing me, again. A project has just built a new Windows (Windows 2008 Std R2) VM and it is running SQL (SQL 2008 R2). The client does not want to pay for the SQL client agent so they are running nightly jobs to backup the SQL d/bases locally to disk as a flatfile/export.
This is on a 7.5.0.5 environment.
With this in mind i added the VM server to an existing policy that uses Flashbackup-Windows for its policy type, backups ALL_LOCAL_DRIVES and it seemed to work okay to start with but now we have noticed when a backup is done it actually triggers a SQL database backup locally on the server (See logs example)
These start times are bang in line with the start time of the backup on NBU. The actual job in NBU always finishes with Status 0 and i see no errorrs in the detailed status for the job. I have even tried a test using VMware as the policy type to see if it makes any difference.
database_name |
backup_start_date |
backup_finish_date |
mins |
type |
Size in GB |
physical_device_name |
master |
2013-07-01 01:30:55.000 |
2013-07-01 01:31:09.000 |
1 |
D |
0.00287 |
{BC90CF83-E0EE-4669-9631-A43F8DB76A08}5 |
model |
2013-07-01 01:30:55.000 |
2013-07-01 01:31:09.000 |
1 |
D |
0.001411 |
{BC90CF83-E0EE-4669-9631-A43F8DB76A08}1 |
msdb |
2013-07-01 01:30:55.000 |
2013-07-01 01:31:09.000 |
1 |
D |
0.023324 |
{BC90CF83-E0EE-4669-9631-A43F8DB76A08}2 |
WebSeries |
2013-07-01 01:30:55.000 |
2013-07-01 01:31:09.000 |
1 |
D |
0.077227 |
{BC90CF83-E0EE-4669-9631-A43F8DB76A08}3 |
sysdb |
2013-07-01 01:30:55.000 |
2013-07-01 01:31:09.000 |
1 |
D |
0.00409 |
{BC90CF83-E0EE-4669-9631-A43F8DB76A08}4 |
master |
2013-07-02 08:09:47.000 |
2013-07-02 08:09:59.000 |
0 |
D |
0.002876 |
{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}5 |
model |
2013-07-02 08:09:47.000 |
2013-07-02 08:09:59.000 |
0 |
D |
0.001411 |
{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}1 |
msdb |
2013-07-02 08:09:47.000 |
2013-07-02 08:09:59.000 |
0 |
D |
0.024099 |
{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}2 |
WebSeries |
2013-07-02 08:09:47.000 |
2013-07-02 08:09:59.000 |
0 |
D |
0.078264 |
{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}3 |
sysdb |
2013-07-02 08:09:47.000 |
2013-07-02 08:09:59.000 |
0 |
D |
0.00409 |
{FD632596-EF30-4DE1-AEE0-3495CCDE5E64}4 |
I am trying to get the server team to advise if the server was built from a clone or a golden image and what has been done recently incase there is something on the server causing the problem. They have told me there is definately no agent installed on it though.
In the meantime i wondered if anyone has any thoughts on what could be causing this?
On think you mentioned though i am not sure about. When talking about inconsistent state backups - "unless the VSS triggered backup records are somehow interfering with their incremental backups.". What did you mean when talking about interfering with incremental backups?
Without having the SQL VSS writer invoked during a VM level backup, the databases captured along will not have been properly quiesced. They will be inconsistent, or some would call it crash consistent, or simply "corrupted".
By allowing the SQL VSS writer to be invoked, the databases in this vm level backup will be consistent and fit for recovery. However, SQL will know that a full backup has taken place.
Depending on how they are doing SQL flat file dumps, some people prefer to have their SQL differential backups reference the full backup that they know they have manually created previously, and not the one created by the sneaky VSS writer sometime after the last manual-full and before the next manual-differential, when nobody was watching. If all their manual backups are fulls, then they will not have this issue.