Blog Archives

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.