Backup all user databases with TDPSQL

Stanislav Kamaletdin (twitter) today asked on #sqlhelp how to backup all user databases with TDP for SQL Server:


My first thought was to use the “*” wildcard, but this actually means all databases, not just user databases.

I ended up adapting a small batch file I’ve been using for a long time to take backups of all user databases with full recovery model:


SQLCMD -E -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')" -h -1 -o tdpsql_input.txt
FOR /F %%A IN (tdpsql_input.txt) DO CALL :perform %%A

GOTO end_batch

tdpsqlc backup %1 full /configfile=tdpsql.cfg /tsmoptfile=dsm.opt /sqlserver=servername /logfile=tdpsqlc.log


Most of the “trick” is in the SQLCMD line:

  • -Q “query” executes the query and returns. I added “SET NOCOUNT ON;” to eliminate the row count from the output.
  • -h -1 suppresses the column headers
  • -o tdpsql_input.txt redirects the output to a text file
With that syntax I create a text file that contains a database name for each line, that I can use in a FOR loop.
FOR /F %%A IN (tdpsql_input.txt) DO CALL :perform %%A
means: “For each token found in the file tdpsql_input.txt, assign the token to variable %%A and pass it to the function named ‘perform'”.
The function “perform” simply invokes tdpsqlc using the parameter %1.
I know that PowerShell would have been a better choice, but I coded this script a long time ago, when PS was not an option for me.
After all, the old DOS batch language still does the trick.

Posted on July 6, 2011, in SQL Server and tagged , , , , . Bookmark the permalink. 17 Comments.

  1. Hello!

    Do you know where I can find initial CMD files of recommended CDM files for TDP?

  2. How did you solve the issue if database is named like “User Db”? Database name is printed out correctly into tdpsql_input.txt, but tdpsql itself will try to backup database name “User”.

    I tried to workaround this by adding ” marks to SQL clause e.g. select (‘”‘ + name + “‘”) from sys.databases which works fine from SQL Server Management Studio, but in SQLCMD it doesn’t seem to work correctly and the whole command crashes.

    • Quick and dirty fix for this problem:

      del sqlinput.sql
      echo SET NOCOUNT ON; SELECT ‘”‘+name+'”‘ FROM sys.databases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) and is_read_only = 0 and state = 0 and recovery_model = 1 > sqlinput.sql

      SQLCMD -S . -i sqlinput.sql -h -1 -o tdpsql_input.txt

      FOR /F %%A IN (tdpsql_input.txt) DO CALL :perform %%A

      GOTO end_batch

      • Thanks for the hint, Mika.
        I suppose that square brackets would work as well. You could obtain the same effect using QUOTENAME on the database name.

  3. Yeah, that might work as well. I forgot say yesterday that also you’ll need increase amount of variables in the tdpsqlc command e.g. tdpsqlc backup %1 %2 %3 because you’re passing more variables into the command.

    Of course, normally you don’t need to modify this script, but I ran into some Sharepoint databases which were named as “User Database Something Important Next Line”. That’s the real reason for modifying this.

  4. Hello, you can try guys SQLgzip (from This is tool which enables you to backup SQL Server database directly to TSM filespace. Only TSM client is needed so no need to buy expensive TDP license. Additionally 32-bit version fully functional is for free !

  5. how do you get notification for backup failure?

  6. I meant, get an email in case the backup job fails, by adding the errorlevel check to this script

  7. I see what you’re saying, but we rather have the backup job itself telling us if it failed that have another process running on the different instances looking for missing backups.

    This is what I have, but when I added these new lines, I broke the lopp… It backs up the first db only and exits.


    cd “C:\Program Files\Tivoli\tsm\TDPSql”

    SQLCMD -E -Q “SET NOCOUNT ON; SELECT name FROM sys.dm_hadr_availability_replica_states ars INNER JOIN sys.databases dbs ON ars.replica_id = dbs.replica_id WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) and role_desc=’PRIMARY'” -h -1 -o tdpsql_input.txt
    FOR /F %%A IN (tdpsql_input.txt) DO CALL :perform %%A

    GOTO end_batch

    tdpsqlc backup %1 full /BackupMethod=Legacy /BackupDestination=TSM /SqlServer=COSQL3SP /SQLAUTHentication=INTegrated /Stripes=8 /Logfile=full_backup_defaultlog


    findstr “error” full_backup_defaultlog
    set rc=%errorlevel%

    if %rc% eq 0 start powershell Send-MailMessage -To “” -Subject ‘COSQL3SP Backup Failed’ -From “Sql@COSQL3SP.nsanet.local” -Body ‘COSQL3SP FULL Backup Failed’ -smtpServer relay.nsanet.local

    • I’m writing from my mobile and I can’t test your script. However, of you’re taking backups every 15 minutes and it’s failing for some reason, do you really want an alert for every failure? I would rather set up an alert every 60 minutes or so looking in msdb.

      However, tomorrow I will take a look from my computer.

    • Now I see that you’re using powershell: why on earth didn’t you translate the script to powershell? Capturing errors is much easier than in dos batches.

  8. I’ll try to figure out how to do it. Thanks

  1. Pingback: The knack » Blog Archive » Tivoli Storage Manager and SQL backup

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: