Generic SQL DB .bch file

[Mark_Hill]

Hey Guys, Im consolodating all the different SQL policies I have in my system.

Im looking at using this .bch file in a default location on each machine:

#*****************************************
#* SQL Backup Batch File *
#*****************************************

OPERATION BACKUP
DATABASE $ALL
MAXTRANSFERSIZE 0
BLOCKSIZE 0
ENDOPER TRUE

##########################################

But after running a couple of tests I get the strange result of the parent job failing with an error 2 and then the child jobs running on as usual.

Im running NBU 6.0mp4 and all the SQL DB's are M$ based.

Can anyone see anything wrong with the batch file, suggest improvements or see a better way of doing things?

Thanks in advance.

[Alex_Vasquez]

Hey there,
This is what I have defined for mine. This should work. I notice you have a couple key lines ommitted.
*********************
OPERATION BACKUP
DATABASE $ALL
EXCLUDE "NorthwindCS"
EXCLUDE "pubs"
EXCLUDE "TESTBAK"
SQLHOST "brweblogsql"
NBSERVER "BRVERITAS"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
ENDOPER TRUE

Try adding nbserver host and sqlhost values to your .bch

[Mark_Hill]

Hey Alex, thanks for the reply.

My main aim is to have a generic bch file on every SQL machine to backup whatever databases it contains.
This way I just list the one bch file in my policy and add in all the clients I need, they all have the same bch file on them.
So I need it not to specify the host or databases, I do like the exclusion of the northwind db though.

Whats the best generic bch file to use?

[Alex_Vasquez]

Hey Mark,
Well, you've got it pretty much right. However, you do need to specify sqlhosts and the nbserver. So you will have a different file for each server, effectively. However, if you specify databases=All then you will be able to back up any database on the sql host regardless of changes to what databases are located on it.
So to answer your question, you pretty much have the generic file already... But it needs those two specifics defined that I mentioned in the above paragrah.
I hope this helps.

AlexMessage was edited by:
Alex Vasquez

[Mark_Hill]

Thanks Alex I will give it a go.

[Alex_Vasquez]

I'm glad to be of assistance and thanks for the points.

[Chad_Wansing]

One thing I will add, I've seen some pretty weird status 2's when the transaction logs get to be too big. If you have any maintenance plans configured for these databases, check to see if you're getting good DB and transaction log backups (I always encourage at least 2 days of maintenance plan backups be kept for the additional layer of recoverability they provide, plus, they get picked up in your regular backups), and if not, you may want to add in transaction log truncation on a DB by DB basis.