cancel
Showing results for 
Search instead for 
Did you mean: 

sql tranlog backup with exlude variable

nbritton
Level 4

I have wrote a script that will run and populate a env var with the databases that are in simple mode :

 

@echo off


sqlcmd -S server\instance  -Q "set nocount on; select name from sys.databases where recovery_model!=1" -W -h -1 -o c:\U...\...\Desktop\sql.txt

 

setlocal EnableDelayedExpansion
set "txt="
set input=c:\....\......\Desktop\sql.txt
for /f "delims=" %%a in (%input%) do (
  set "txt=!txt!%%a,"
)
set "txt=!txt:~0,-1!"
>new.txt echo !txt!

echo %txt%
setx NB_SQL_DATABASE_EXCLUDE %txt%

echo %NB_SQL_DATABASE_EXCLUDE%

 

and i have the following script for a tranlog backup:

 

BATCHSIZE 3
OPERATION BACKUP
DATABASE $ALL
SQLHOST "sqlhost"
SQLINSTANCE "SQL2008R2"
NBSERVER "master01"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTTYPE TRXLOG
NUMBUFS 2
ENDOPER TRUE

 

But when the policy runs i still see it running the excluded databases by the enviornment variable.  

 

any thoughts?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Chirag_Patel1
Level 3
Employee

I ran sqlcmd  you have written above and got the list of system DBs which should be excluded (having simple recovery model) in sql.txt. then added one of the user database i wanted to exclude in sql.txt file. then ran the script you have created. i verified that User environment variable NB_SQL_DATABASE_EXCLUDE got set properly but databases are ',' seperated then i changed the seperator to ';' (which NetBackup for MSSQL agent expects).

Then i reopened the MSSQL agent GUI and ran the same batch file as you have mentioned above (obviously after modifying the SQLHOST, SQL instance and NBSERVER). It is excluding the databases specified in NB_SQL_DATABASE_EXCLUDE User environment variable.

I didn't specify NB_SQL_DATABASE_EXCLUDE in system environment variable. 

I ran above test with administrator. I also tried with another user as well and everything is working as expected.

I looked at the dbclient logs you have provided and it has excluded the 'JAMS' database for trxlog backup.

Can you manually specify User environment variable NB_SQL_DATABASE_EXCLUDE and database names with ',' seperated and try the backup?

View solution in original post

6 REPLIES 6

Will_Restore
Level 6

If the parent job exit code is 0 then it is skipping them; if it's 1 then not.

 

If the latter, post dbclient and bphdb logs as attachments

Also, output of  echo %NB_SQL_DATABASE_EXCLUDE%

nbritton
Level 4

bphdb:

 

16:46:57.683 [8728.3200] <2> logparams: -sb -rdbms sql_server -S linux6160 -to 300 -c ZZ_Test_SQL_TRAN -s Tran -clnt oma00vcdba02 -FULL -kl 28 -b oma00vcdba02_1401313601 -jobid 1232 
16:46:57.696 [8728.3200] <4> bphdb: INF - BACKUP START 8728
16:46:57.697 [8728.3200] <4> bphdb: INF - CONTINUE BACKUP message received

16:46:57.697 [8728.3200] <4> bphdb: INF - Processing tranlog-bkup.bch
16:47:57.704 [8728.3200] <4> bphdb: INF - bphdb still working.
16:48:57.707 [8728.3200] <4> bphdb: INF - bphdb still working.
16:49:14.539 [8728.3200] <16> bphdb: ERR - command failed: the requested operation was partially successful (1)
16:49:14.539 [8728.3200] <4> bphdb: ERR - exit status  :  (1)
16:49:14.539 [8728.3200] <16> bphdb: ERR - bphdb exit status = 1: the requested operation was partially successful

16:49:14.540 [8728.3200] <4> bphdb: INF - EXIT STATUS 1: the requested operation was partially successful

 

C:\Users\tsmbackup\Desktop>echo %NB_SQL_DATABASE_EXCLUDE%
JAMS

 

However if i pull up the list of envionrment vars under user vars, i see its set to :

master;tempdb;msdb;JAMS 

I dont see it under system vars at all

 

I am setting it with setx and i have tried to set it with set....

Will_Restore
Level 6

What does your input file  Desktop\sql.txt  contain?   I don't have sqlcmd to test that part. 

Chirag_Patel1
Level 3
Employee

I ran sqlcmd  you have written above and got the list of system DBs which should be excluded (having simple recovery model) in sql.txt. then added one of the user database i wanted to exclude in sql.txt file. then ran the script you have created. i verified that User environment variable NB_SQL_DATABASE_EXCLUDE got set properly but databases are ',' seperated then i changed the seperator to ';' (which NetBackup for MSSQL agent expects).

Then i reopened the MSSQL agent GUI and ran the same batch file as you have mentioned above (obviously after modifying the SQLHOST, SQL instance and NBSERVER). It is excluding the databases specified in NB_SQL_DATABASE_EXCLUDE User environment variable.

I didn't specify NB_SQL_DATABASE_EXCLUDE in system environment variable. 

I ran above test with administrator. I also tried with another user as well and everything is working as expected.

I looked at the dbclient logs you have provided and it has excluded the 'JAMS' database for trxlog backup.

Can you manually specify User environment variable NB_SQL_DATABASE_EXCLUDE and database names with ',' seperated and try the backup?

Will_Restore
Level 6

Good catch!  Modify this line in the script above

  set "txt=!txt!%%a,"

change the comma to semicolon and it should work

  set "txt=!txt!%%a;"

 

yes

Will_Restore
Level 6

@ nbritton, any luck with this??