1

How can I take backups of databases that are not present in Availability Groups?

Can I use a single maintenance plan for both AG dbs and non AG dbs?

Should the copy_only option be enabled for non AG dbs too? I know Ola Hallengren's script has an option to include non-AG databases, but I want to stick with the in-built plans as of now.

The databases that are not in the AG are on the primary node. Should I create a different maintenance plan for them? All databases are on the primary including the non-AG dbs.

I understand copy_only option allows us not to break the LSN chain. This is a 2 node setup with a primary and secondary replica.

These are vendor based databases, so the business is fine with a backup of at least a day earlier from the backup schedule.

I checked the job history of the non-AG databases. There are only 2 of them, and I see the backup files being copied to the backup drive. But the job history still shows that "The job failed at step 1 - job was invoked" . I am not sure what's wrong. I can see the backup files in the drive.

This is the message I see in the history:

The job failed.
The Job was invoked by Schedule 1100 (DailyBackup_NonAAG).
The last step to run was step 1 (Subplan_1).

I am also adding the creation start and finish days of a database to give you a clear idea:

database_creation_date  backup_start_date   backup_finish_date
2016-12-22 20:31:43.000 2017-04-05 00:08:34.000 2017-04-05 00:34:31.000
  • Creating two separate maintenance plans is not going to hurt. You can have one for AG databases and other for non AG databases – Shanky Mar 29 '17 at 4:59
1

It really Depends,

Technically, as others have mentioned, you can use one built in maintenance plan to backup all of the databases. Regardless if they are in an AG or not in an AG.

But having it all in one plan, and having that plan fail, means you may miss some SLAs. There is a question I ask myself when setting up backups: "What is the Restore Strategy and the SLA?" Your backup strategy should account for your restore and recovery requirements in the SLA. Can you restore exactly the amount of data that business requires, in the time frame required? Imagine if your one maintenance plan fails, and only backups up half of the databases one night. What happens if a disaster occurs between the failed backups and the next backup cycle? Can you recover the data and get the business back online? It depends on your SLA. That should help you decide if you need one job, two jobs, or a different strategy entirely.

Also Since you mentioned, "Do i need to use copy_only?", am I right in assuming you have Databases acting as principals/primaries, and other in an AG group acting as secondary replicas? If so you can still techincally use one job but you need to understand what copy_only does. The article has some good highlights.

  • If they are tiny, their arent that many, and you can monitor the maintenance job to make sure you always have at least that day earlier backup you should be okay. Personally i like breaking the jobs up (leveraging Ola's and SQL agent) to back multiple databases in parallel. this way If the job fails i only miss a db instead of multiples. But it really depends on your individual SLA, how many dbs there are, how big, and how fast your backups take. – mokszs Mar 29 '17 at 18:48
1

I actually figured out yesterday about this. Sorry for not getting back to you early enough. The backups were running fine but the cleanup of older backups were failing in the plan . That is what was causing the job to fail.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.