How to find MS SQL database not excluded from file system backups using bplist

[Nicolai]

Very often MS SQL databases don’t get excluded from the ordinary file system backup. This result in huge incremental backup, good for absolutely nothing since the SQL database is not consistent at point of backup. Luckily it’s very easy to be on top on this issuec and find those database files using Netbackup bplist utility.

This is a Korn Shell script that does the job:

#!/usr/bin/ksh
# A simple script that can assist the Netbackup admin finding MS SQL database files not excluded from the regular file system backup
#
POLICY=$1
if [ $# -ne 1 ]
 then
 echo "Policy name missing !!"
 exit 1
fi

# Calgulate how many days back we look. Adjust the "2 days ago" section to go longer back.
EPOCH_DATE=`date --date='2 days ago' +%s`
BPLIST_STRING_DATE=`date -d @${EPOCH_DATE} +%m/%d/%Y `

# Retrieve a list of client in the policy choose. Since bplist terminate each line with a null byte we need to use sed
# to get rid of it else grep doesn't match any mdf ndf or ldf files.

/usr/openv/netbackup/bin/admincmd/bppllist ${POLICY} | grep CLIENT | awk '{ print $2 }' | while read CLIENT
do
 echo "Processing ${CLIENT}"
 echo
 /usr/openv/netbackup/bin/bplist -B -C ${CLIENT} -R -l -t 13 -s ${BPLIST_STRING_DATE} / | sed 's/\x0//g' |  grep -i -a -e  "\.mdf$" -e "\.ndf$" -e "\.ldf$"
 echo
done

The script assumes the policy you specify is type 13 (Windows). If you run the script again a type 0 (standard) policy  if will receive the usual “EXIT STATUS 227: no entity was found”.

Output from script:

Processing srv1.acme.com

Processing srv2.acme.com
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb2.ndf
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb3.ndf
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb4.ndf
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb5.ndf
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb6.ndf
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb7.ndf
-rwx------ root      root        524288000 Mar 15 06:53 /D/SqlData/TempDB/MSSQLSERVER/tempdb8.ndf
 

find_sql.ksh_.txt ‏1 KB