November 29, 2012 at 8:54 am
I had a standard backup plan for a moderately used database, full backup once a week, differential weekdays, log backups twice a day. A little over a month ago, my differential backups started failing because, it said, there was no current full backup.
The full backups themselves were functioning correctly. Furthermore, if I did a full backup, I could then run the differential. But a day or two later, it would fail again.
I am on a need-to-know basis, and usually not even then, so it was quite by accident that I learned the sql server is now a virtual machine or running on a virtual machine, and that this was implemented one day before the backups started failing.
It can't just be a coincidence. I just don't know why that would have an effect.
Any suggestions? I need to get the backups working again. Going to daily full backups is not an option because there isn't enough space. And they're not going to abandon VM just because it's complicating my life.
November 29, 2012 at 8:57 am
Hi,
When you said you could do the differential bakcup following the full backup how were you running this? Through the GUI? If it was, it could be that your sql service account doesn't have permissions to write the dif backups to the destination as specified in your agent job.
Thanks,
Simon
November 29, 2012 at 9:57 am
Also verify the destination path is still valid in your Agent job.
November 29, 2012 at 10:41 am
Is the full backup still available? Or has it already been removed from the backup location?
Side note: Did they recently add more space to the VM? Often times when additional space is allocated to a drive in a VM, it must be restarted of SQL Server tends to freak out about it. However, if this were the case, you wouldn't be able to run any type of backup.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 29, 2012 at 10:47 am
What's the error?
Are there any full machine backups running? Possibly via the VM host?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2012 at 11:34 am
I had jobs set up to run the backups. Lately I've been running the backups by manually running the jobs because I'm in test and debug mode and can no longer trust the system. But before this happened, the jobs were run, successfully, by the Sql Server Agent.
If you're suggesting that the Agent no longer has rights, there have been a very few successful runs in which the agent handled it on its own. Typical scenario: I run the full backup manually and confirm that it works, run the differential manually and confirm that it works, come in the next morning and see the diffential ran successfully during the night as scheduled, so I think, okay, whatever, it's fixed, come in the next day and see a big red X because it's telling me a full backup doesn't exist, although the .bak file is sitting right there where I left it. Also, the full and log backups continued without error.
It can't be a rights issue, and the files were not being moved (or if they were because of this VM imaging process) they were getting moved back).
November 29, 2012 at 11:58 am
Here is the error from the job history log:
failed with the following error: "Cannot perform a differential backup for database "MCRInc_2011App", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have passed on your other questions to our network engineer. Thank you
November 29, 2012 at 12:08 pm
Something's taking full backups of the DB other than your SQL agent backup jobs. I've seen that with BackupExec, can probably happen with other VDI backups. Whatever they're using to backup the VM should be set to exclude the SQL Server database files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply