cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Backup - How to/Best practice!?

MariusD
Level 6

Hello,

 

i need some ideas for the following scenario. How i can better use the NetBackup and fewer policies to backing up a SQL Server.

 

Master: Windows 64bit 2008 R2, NBU 7.5.0.3

Client: Windows 64bit 2008R2, NBU 7.5.0.3

SQL: 2008 R2

 

The SQL Server has 3 SQL instances and each instance has more DB. The backup have to run:

Daily Backup:      Mo-Fr                             - Retention 35 Days

Weekly Backup:  Sa(Su)                            - Ret. 60 Days

Monthly Backup:  last Sa(Su) of the Month  - Ret 1 Year

Yearly Backup:    last Sa(Su) of the Year     - Ret. 7 Year

 

This mean: Need to create for each instance a policy and for each retention period a policy:

SQL_Instance1 -> Daily_Policy - Weekly_Policy - Monthly_Policy - Yearly_Policy

SQL_Instance2 -> Daily_Policy - Weekly_Policy - Monthly_Policy - Yearly_Policy

SQL_Instance3 -> Daily_Policy - Weekly_Policy - Monthly_Policy - Yearly_Policy

and for SQL Transaction_Logs another 12 Policies.

 

Total: 1 Systemfile Windows policy and 24 SQL policy. And this just for one Server.

How to simplify this policy-tree?

 

Have try to use $ALL for INSTACE and DATABASE but ends with error nr. 2. When use a backup script for each instace, than its working. 

 

Waiting your ideas. Thnaks a lot.

 

Regards,

Marius 

 

 

7 REPLIES 7

MilesVScott
Level 6
Certified

You may want to have your DBA's create SQL maintenance plans to back up the databases. Then you can take a regular filesystem backup of the .bak files and .trn files.

Douglas_A
Level 6
Partner Accredited Certified

For large environements like this we have the SQL admins init the backups

 

We do this as stated above using the SQL maintenance to start the backup script..

 

So we create a single SQL policy with the following schedules, logs, full, incr, monthly.

On the sql host create a script for each DB if you like or one for multiple instances, then use the dbbackex string in the sql maintenance to start the backup. Syntax below:

dbbackex -f file [-p policy][-u userid][-pw password] 
[-s server][-np]

**NOTE: you can omit the -u and -pw if you have the DBA configure the login creds using the MSSQL agent on the client.

The key here is you need multiple scripts one for each type of backup being done so that it calls the correct schedule. With this method we backup over 100 SQL instances and who knows how many databases with a single policy.

 

EDIT: Almost forgot your last part.. the OS backup can be done with another policy for just the OS... meaning exclude all MDF, LDF and NDF files this shoudl pickup all filesystem data and leave the SQL data to the agent. This single windows policy can hold all your server os backups with a single set of schedules.

Hope this helps.

 

 

MariusD
Level 6

Hello Douglas,

one question: How is looking the NetBackup SQL Policy (Schedules, Backup Selection) and also the backup script. Please provide me a screenshot or exemple.

 

Thanks,

Marius 

 

 

florin_s
Level 4
Certified

hi,

"SQL_Instance1 -> Daily_Policy - Weekly_Policy - Monthly_Policy - Yearly_Policy"

instead of that you can use one policy with daily/weekly/monthly/yearly schedule and on each schedule override the storage location and select an SLP with a predfined retention(for disk backup) or same process for tape backup and you select a volume pool and set the retention here.

and instead of "All" for instances you can enumerate them in the same file

regards,

florin

 

MariusD
Level 6

Hi,

 

i can do this thing on Windows Policy Type but how to do this on MS-SQL Policy type?

Under the Schedules, I need to set the Automatic and Application Backup. 

By "automatic backup" i can set just the backup window and by "application backup" the all another things (storagelocation, volume, retention ..). And the automatic schedule type start the application shedule type. 

How ist looking the "SCHEDULES" with more Automatic Backup and Application Backup schedules? i can't understand. 

 

screenshot from my sql policy (daily)

 

 

Can you post a screenshot with a exemple? Thanks.

florin_s
Level 4
Certified

hi,

sorry, i've answered from memory, i totally forgot that this option is not available for SQL policies :(

regards,

florin

 

Douglas_A
Level 6
Partner Accredited Certified

You should have a MS-SQL policy type and all schedules as Application backup with a window open 24/7.

 

The backup will be initiated by the SQL maitenace schedule. a script will be generated on the SQL client with the databases it will be backing up. you will have to add a line to the generated sql script "NBSCHED <scheule name>" this can be anywhere in the policy but must exist for each batch job you see in the script.

Example script:

OPERATION BACKUP
DATABASE "MASTER"
SQLHOST "HOSTNAME"
NBSERVER "MASTER SERVER"
NBSCHED "schedule name" << manually add to each script
MAXTRANSFERSIZE "6"
BLOCKSIZE "7"
NUMBUFS "2"
ENDOPER TRUE