excluding DBs in Simple recovery mode from log backups if SQLINSTANCE $ALL is used in backup script

[akash_viswan]

Hi,

I am trying to backup SQL Server Databases , and we are using SQLINSTANCE $ALL in out backup script. Log backups are failing with Status 1 , saying some DBs can't backed up because they are in Simple recovery mode. How to Exclude those DBs from LOG backups.

Akash

[Marianne]

Extract from NBU for SQL Admin Guide: 

You can also exclude individual databases from backup by creating a Windows

environmental variable NB_SQL_DATABASE_EXCLUDE. For the value of the

variable, specify a list of database names.

For example, consider the following batch file:

SQLINSTANCE $ALL

OPERATION BACKUP

DATABASE $ALL

NBSERVER "BEARING"

MAXTRANSFERSIZE 6

BLOCKSIZE 7

NUMBUFS 2

ENDOPER TRUE

You can exclude the databases "master," "accounting," and "pubs" with the

NB_SQL_DATABASE_EXCLUDE environmental variable. For the value of the

variable, indicate the databases you want to exclude. Separate the database names

with semi-colons.

MASTER;ACCOUNTING;PUBS

The NB_SQL_DATABASE_EXCLUDE variable is applicable only for a batch file

that has DATABASE $ALL. It performs the same function as the keyword and

value pair EXCLUDE <database>. If both variables are used, they augment each

other to determine which databases to exclude.

[akash_viswan]

Marianne, I want DBs to be excluded from LOG backups only, I need Incrimental and full backup to run. Will that happen if I exclude DB like this? Is there any way to exclude it from script?

[Marianne]

Your log backups should be in a separate script with DATABASE $ALL as selection, followed by 
EXCLUDE <database> in the script.

So, instead of NB_SQL_DATABASE_EXCLUDE environmental variable, use the EXCLUDE <database> in the script.

[akash_viswan]

But I am having multiple instances in server and I don't want to create separate script per Instance.  Is there any other way to add multiple instance selection in same script.

[Marianne]

Have you tried to read through the section in the manual that I have quoted from above?

You can see that it caters for SQLINSTANCE $ALL and DATABASE $ALL.

[akash_viswan]

Went through it couple of time, looks like there is no option to exclude a DB in an Instance using the script .

[Marianne]

So did I.

What I saw was the ability to use 'EXCLUDE <database>' in the script.