Backup succeeded but backup file nowhere to be found

SQL Server Administration
1

Hi, my dear experts,

Please help me figure out what went wrong.

I created these maintenance plan backups (full, diff and tlog), and they have been running "successfully", at least that’s their histories have shown. However, now upon a closer look, there were no backup files existed on the destination locations.

Months ago, they were there.

So here are what I have tried:

Cleanup job wipe them out? No, I run them ad hoc, still no file. And the job completes too fast, within a minute.

Destination permission issue? Unlikely: 1) it did not error out; 2) I verified that service account is on that network share drive and is the same account in history message Exec as user; and 3) my ad hoc run was able to create a subfolder on the destination with current time stamp.

High Availability issue? Maybe but why? My initial plans were setup before the AG was hookup, and now this box is the primary on a two node cluster. Following note is from the maintenance screen:
The selected backup type is not supported on a "Always on secondary replica"...
And sending me to Microsoft webpage : http://go.microsoft.com/fwlink/?LinkId=208213
Active Secondary's: Backup on Secondary Replicas.

But I am sure, I am trying to backup the primary.

Thanks!
Hommer

2

On the local sql box create a folder c:\backup

Then backup to that location. See if it's a network issue or something else.

3

Vinnie,

Thank you for your reply.

I did what you have suggested, but the result is the same. Folders were created but they are empty, no bak file inside.

4

Obvious question but did you make sure to specify to use the .bak extension for your backups? There is an option in the maintenance place where you need to specify that.

1 Like
5

mfemenel,

Thanks!

I do have that extension there. I also have another backup for transaction log. They both failed to create a file at destination.

This is on SQL 2012 Enterprise edition.

I have another set of database with similar characteristics, which has been successfully backing up.

The only obvious difference is this set of database is for SharePoint 2013. However, SharePoint itself did not create the issue, because I had backed them up before they were hooked up for High Availability.

Any other ideas?

Thanks!

6

My colleague figured this one out.

It is caused by an AG setting conflicts with maintenance plan.

Here is the msdn.

2 Likes
7

That was my problem too and I could'nt find the reason, thanks for your answer

8

Hi,
I checked "the for avalibity database, ,gnore replica priority for backup and backup on primarry settings. "
After, it worked.

Backup
Backup865×589 93.2 KB

9

The whole idea for this is so you can setup your jobs on both nodes - and only one of the nodes will perform the backups for AG databases.

If you set the backup preference and priority in the AG - that will be used to determine which node to perform the backups. One of the settings is primary only - if you set that in the AG and create the MP on both nodes then only the primary node will be used for AG databases.

Ignoring that setting means you need to setup and create a plan on the secondary - and disable the jobs until you failover. If you setup a plan and let it run using the above it will run backups on both nodes - which you don't actually need.

10

I need only one server for backup. In this scenario is it a acceptable idea this checkbox checked.

11

I wouldn't accept it - but if you are okay with the fact that you will not have any backups if/when you failover to the secondary, I guess it is okay.

12

Okey I will try to the best and asab I will change it.
Thanks a lot for your replay.

13

Thanks a lot I applied your suggestions