BEMCMD - Selection List - Select SQL Databases

Update.

Got this piece working, BUT ran into another issue.

First, let me tell you how this got fixed.

I used the following syntax:

SQL2S1 = SERVERNAME.domain.com

SQL2DB1 = A*

(I pretty much accidentaly lied in my initial post. What happened was I was using SQL1 instead of SQL2S1; this is confusing in the manual itself, or at least it was how I read it in the help, that SQL2S1 was for SQL 2000). Anyway, it works with SQL in general.

The issue I am facing now is explained next. I can setup the following selection:

SERVER.domain.com\*.*

SERVER.domain.com\A* /EXCLUE

What this does - backs up all databases, EXCEPT databases that start with letter A.

Now, I can do this via GUI, but I cannot get it done via script.

This is the last piece of my puzzle. I would appreciate if anyone would provide any insight of how to get *.* with exlusios created as a single selection list.

There is no way to do this using BEMCMD.

This is do-able as follows, you screate a SQL Query that runs in SQLCMD and returns the names of the databases that start with A*

This Query Works in SQL to give you what you need, you may need to format it appropriately to run inside a batch:

SELECT MstSysDB.[Name]

FROM [SERVERNAME].[Master].[dbo].[sysdatabases] MstSysDB

WHERE MstSysDB.[Name] <> N''TEMPDB'' AND MstSysDB.[Name] LIKE "A%"

ORDER BY MstSysDB.[DBID] ASC'

This Batch File has a place holder for the Command Line Version of the Above Query,(you will need to run the Command Line SQl interface and based ont he output from that you may have to change the for loop which parses the output as well.)

The Query Works and this batch File will create the selection list if the output from the SQL command is formatted correctly.

:: Script Name: BE_SQLSelection_List.bat

:: version 1.0

:Begin-Script

@(

 ECHO OFF

 SETLOCAL

 SET "ServerName=SERVERNAME"

 SET "SelectionList=C:\SQLDBSelectionList.txt"

 SET "SQLID=1"

 SET "SQLServerID=1"

 SET "_SQLCMD=SQL COMMAND Running The Query Goes Here"

 SET "BEUserID=10011001-1001-1001-0101-010101010101"


)


CALL :Main


:End-Script

(

  ENDLOCAL

  GOTO :EOF

)


::Subroutines Below:

:Main

ECHO.>"%SelectionList%"

ECHO.[Machine]>>"%SelectionList%"

ECHO.MACHINE1=\\%ServerName%,{%BEUserID%}>>"%SelectionList%"

ECHO.>>"%SelectionList%"

ECHO.[Selections]>>"%SelectionList%"

ECHO.>>"%SelectionList%"

ECHO.SQL%SQLID%S%SQLServerID%=%ServerName%>>"%SelectionList%"


FOR /F "Tokens=*" %%R IN ('"%_SQLCMD%"') DO (

FOR %%D IN ("%%~R") DO (

CALL :Sub_SQL-Out "%%~D"

)

)


ECHO.>>"%SelectionList%"

ECHO.>>"%SelectionList%"

ECHO.[Availability]>>"%SelectionList%"

ECHO.>>"%SelectionList%"

ECHO.[Media_Server]>>"%SelectionList%"

GOTO :EOF


:Sub_SQL-Out

SET /A "DBCount+=1"

ECHO.//SQL%SQLID%DB%DBCount%=%~1>>"%SelectionList%"

GOTO :EOF

/* BACKUP SELECTION LIST SCRIPT */

/* operations: 220 */


[Machine]

MACHINE1=\\SERVERNAME,{10011001-1001-1001-0101-010101010101}


[Selections]


SQL1S1=SERVERNAME

SQL2DB1=Alf

SQL2DB2=Alpha

SQL2DB3=Another DB

SQL2DB4=Armor

SQL2DB5=Around



[Availability]


[Media_Server]

Add your BEMCMD command to then import the Selection list into MAIN right before "GOTO :EOF"