Forum Discussion

nbritton's avatar
nbritton
Level 4
11 years ago

sql tranlog backup with exlude variable

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?

 

 

Backup and Recovery
NetBackup
Windows Server (2003-2008)
  • Chirag_Patel1's avatar
    Chirag_Patel1
    11 years ago

    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's avatar
    Will_Restore
    Level 6
    11 years ago

    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's avatar
    nbritton
    Level 4
    11 years ago

    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's avatar
    Will_Restore
    Level 6
    11 years ago

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

  • Chirag_Patel1's avatar
    Chirag_Patel1
    Level 3
    11 years ago

    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's avatar
    Will_Restore
    Level 6
    11 years ago

    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