Video Screencast Help

BEMCMD - Selection List - Select SQL Databases

Created: 01 May 2013 | 6 comments

I will appreciate the help if anyone could help me figure out how to select certain databases (applying the template, explained below), and not have *.* (all databases).

Right now we do everything via GUI, but I have a task at hand that will cost me a lot of time if I need to do it manually.

We select databases to backup that in the Selection List looks like this:

SERVERNAME.domain.com\A*

Now, this backs up all databases that start from letter A. The template "A*" in GUI is added in "Files" field when inserting what to backup

I tried running the script to have this done for me, but all I can do successfuly is to create a *.* selection

I cannot figure out how to specify which databases to backup using the template I need, in this example: A*

So far I tried using:

- SQL1 = SERVERNAME.domain.com <---- creates SERVERNAME.domain.com\*.*

- SQL1 = SERVERNAME.domain.com\A* <---- fails

- SQL2S1 = SERVERNAME.domain.com\A* <---- fails

- Two entries together, which fails

SQL2S1 = SERVERNAME.domain.com

SQL2DB1 = A*

 

SQL Server version = 2008 R2

OS = Windows Server 2008 R2

BE version = 2010 R3

 

Thank You!

Operating Systems:

Comments 6 CommentsJump to latest comment

GAccount's picture

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.

pkh's picture

You cannot restore SQL databases using BEMCMD.

GAccount's picture

I am not trying to restore the SQL database.
I am trying to create the Selection List using BEMCMD for a future SQL backup job.

pkh's picture

There is no way to do this using BEMCMD.

QSquared's picture

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
 
The Result Will be a Selection List similar to the example below:
 
 
/* 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]
QSquared's picture

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