SQL Server 2014 – How to Fix Backups Failing for All Databases

backupsql serversql server 2014

Here is the error message in its entirety:

Failed:(-1073548784) Executing the query "BACKUP DATABASE [MCM] TO DISK = N'F:\Backup\MCM\M…" failed with the following error: "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.

There is no further information. No backups for any database on the server are working right now, but they did succeed the day before. There is plenty of backup space. One thing to note, though I'm not sure if it's relevant, is that there was a transaction stuck in a Killed/Rollback state the day before; it was blocking on a remote procedure call to a Sybase database. The service was restarted to try to "fix" this stuck transaction (by someone I warned beforehand against doing such a thing).

What do I need to do to fix this situation so that I can get the backups working again?

Best Answer

I should have written this long ago, but better late than never.

How did I resolve the issue? Each backup attempt created a dump file which I analyzed using WinDbg. These files indicated that the error originated in the sqlmin.dll assembly. I did a search for hotfixes for that assembly, found some, applied them, and after a reboot, the backups started working again.

For anyone who comes across weird SQL Server bugs like this, try looking at the dump files for clues.

Related Solutions

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

Sql-server – My Backups Broken

Create a domain user that has read/write privileges to both the network share as well as NTFS read/write permissions on the folder.

In Configuration Manager, set the SQL Agent user to this domain user. SQL Agent is the account that will execute the backup as a task. The user does not have to be a domain admin, as long as it has the NTFS permissions it needs. You can also make it an Active Directory Managed Service Account, but that's probably down the road from now.

You should be able to keep the SQL Server Service user as the local NETWORK SERVICE account, though if you were to run this script from SSMS, it will probably still fail. You'll want to schedule a one-time backup job from within the SQL Server Agent jobs in SSMS to get a real test of the configuration.

Another option altogether would be to backup to a local drive and have a Windows scheduled task move the file to the network share. This would prevent the backup from failing if it couldn't reach the other machine for any reason.

EDIT :

For those just finding this, you should know that on any machine when it uses its local SYSTEM or NETWORK SERVICE account, other systems see it as the machine name with a dollar sign ($) after it.

E.g. :

MyDomain\Computer1
User: NETWORK SERVICE

MyDomain\Computer2
User: MyDomain\Computer1$

It even works on machines that are not on a domain at all (so long as they can reach each other on the network).

Best Answer

Related Solutions

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Sql-server – My Backups Broken

EDIT :

Related Question