Backup all user databases with TDPSQL


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

Twitter

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:

@ECHO OFF

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

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

:end_batch

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.
About these ads

Posted on July 6, 2011, in SQL Server and tagged , , , , . Bookmark the permalink. 9 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 SQLgzip.com) 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 !

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 521 other followers

%d bloggers like this: