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:

@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.

Archived WordPress comments (16)

Historical comments from the original site; this archive is read-only.

Stanislav December 6, 2011 at 13:40
Hello!

Do you know where I can find initial CMD files of recommended CDM files for TDP?
1. spaghettidba December 7, 2011 at 09:32
  I'm sorry, I don't know exactly where you can find them. Have you tried searching IBM's support site?
Mika June 8, 2012 at 16:34
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.
1. Mika June 8, 2012 at 18:06
  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
  1. spaghettidba June 9, 2012 at 08:49
    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.
Mika June 9, 2012 at 14:41
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.
1. spaghettidba June 9, 2012 at 16:09
  Interesting finds. Thanks for sharing.
  Sharepoint is a pain in many ways. Or maybe I should say "sharepain" :-)
Andrzej June 17, 2013 at 07:58
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 !
livia June 17, 2015 at 19:46
how do you get notification for backup failure?
1. spaghettidba June 17, 2015 at 19:52
  You mean for tdp? I wouldn't rely on that, I would rather check the backup status from msdb.
livia June 17, 2015 at 19:55
I meant, get an email in case the backup job fails, by adding the errorlevel check to this script
1. spaghettidba June 17, 2015 at 20:07
  Getting the errorlevel is not easy but reliable. You can pull backup history from msdb easily.
livia June 17, 2015 at 20:26
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.

@ECHO OFF

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

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

:end_batch

findstr "error" full_backup_defaultlog
set rc=%errorlevel%

if %rc% eq 0 start powershell Send-MailMessage -To "l.peoples@juiceplus.com" -Subject 'COSQL3SP Backup Failed' -From "Sql@COSQL3SP.nsanet.local" -Body 'COSQL3SP FULL Backup Failed' -smtpServer relay.nsanet.local
1. spaghettidba June 17, 2015 at 20:35
  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.
2. spaghettidba June 17, 2015 at 20:37
  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.
livia June 17, 2015 at 20:47
I'll try to figure out how to do it. Thanks

See also