cancel
Showing results for 
Search instead for 
Did you mean: 

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

akash_viswan
Level 2
Certified

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

7 REPLIES 7

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 2
Certified

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
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 2
Certified

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
Moderator
Moderator
Partner    VIP    Accredited Certified

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
Level 2
Certified

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

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

So did I.

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