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
Best Answer
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.