Multiple Sshcedule for SQL Backup

[Twinkle_Sapra]

For oracle bakcup, we can use multiple policy with multiple scrips.

How to do in SQL backup?

Scenario :

I have  policy for SQL Backup having 2 schedules.

1. Default Appliacation backup.(1 month).
2 . Full backup ( Retetion 1 month).

How to add monthly schedule with retention of 1 year. Either I have to add monthly schedule in same policy or i have to craete separate policy.

[Deepak_W]

 If your backup selection in same for all the schedules then I would say that add one more schedule in your existing policy.

But if your backup selection is going to change to the monthly backup then create new policy. 

Hope this resolves your query

[David_McMullin]

I was told that both rman and sql policies should have only two schedules, one calls the other.
If you need multiple retentions, you should copy the policy and edit teh schedule.

[Will_Restore · Accepted solution]

Separate policy needed for differing retention for Default Application Backup.

[Dion]

Hi

You do need to have a single SQL policy per retention.  Each policy will generally contain two schedules - one Automatic Backup schedule and one Application Backup schedule.

The Automatic Backup schedule is the schedule that will trigger the backup (the retention on this is not used to determine how long the backup will be kept).  You can have multiple Automatic schedules with different retentions but this will only affect when the backups are sheduled and will prioritise which one will run when they conflict.

The Automatic Backup schedule when started will start the Application Backup shedule in the policy which will run the actual SQL backup and will start the .bch file specified in the Backup Selections part of the policy.  The retention on this schedule will be the one that is used to determine how long the backup is kept.

To have multiple retentions, you will need multiple policies (one for each type of retention).  What I've done is to group my SQL policies by retention and SQL instance number.  This way I can have daily, weekly and monthly for all servers that have a default instances and then daily, weekly and monthly for each instance number.

This solves my problem for both single node servers with one or more instances and for multiple-node clusters with one or more instances aswell where different .bch files need to be referenced.

Hope this helps

[jsola]

I always found it easier to manage servers individually with 1 to 1 policy to server approach. I know it can get overwhelming but it gives you complete control over each server and what you want to do with each individual server. For example, we do our SQL servers using the SQL agent and backup the actual live database. Also, as a redundant measure and third option we also backup the SQL .bak files that are exported by the SQL admins. This gives us two methods of recovery in case one fails. Also, we like to verify our backups are good by doing quarterly restore tests of a random database and a random date. I hope this helps align your final result!

[Twinkle_Sapra]

Thanks to ALL for your valuable inputs.

[Twinkle_Sapra]

Is there any way to define policy name in SQL script like RMAN NB_ORA_POLICY to avoid conflict

[Dion]

I've heard about the 1 policy per server idea which definitely has its merits when it comes to control. 

My issue is that you could have an almost unmangeble amount of policies (and yes, I do script most of my stuff) even in a medium sized company.

Take the following example...
3 node SQL cluster
3 SQL instances, each in separate cluster groups
Hourly transaction logs, Daily differentials, Weekly and Monthly full backups

This translates into the following policies
3 policies for the windows nodes
1 policy for each of the SQL backups that require different retentions or scripts (+-12)
4 SQL scripts per node (12)

That's 15 policies for this group of servers and 12 SQL scripts.  Multiply that by 50 or 100 SQL server nodes and the number of policies grows pretty quickly.

Anyway, just my 2c worth

Later

[Alexis_Gonzalo_]

This document is very useful:
http://seer.entsupport.symantec.com/docs/258640.htm

With it, we created a single SQL policy for both daily (monday to friday ) and weekly (saturday-sunday) backups.
But for monthly backups (done once in a month), we had to create a second policy because the schedule window was the same as the weekend window.

Also, you can use the NBSCHED to specify the Application Backup Schedule. But I can't guess an scenario where this could be useful (maybe if the backups were initiated from the SQL server instead of from the NB Server).