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 %%Ameans: “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 backup, batch, TDP, TDPSQL, Tivoli. Bookmark the permalink. 17 Comments.
spaghettidba 
Hello!
Do you know where I can find initial CMD files of recommended CDM files for TDP?
I’m sorry, I don’t know exactly where you can find them. Have you tried searching IBM’s support site?
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.
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.
Interesting finds. Thanks for sharing.
Sharepoint is a pain in many ways. Or maybe I should say “sharepain” 🙂
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 !
how do you get notification for backup failure?
You mean for tdp? I wouldn’t rely on that, I would rather check the backup status from msdb.
I meant, get an email in case the backup job fails, by adding the errorlevel check to this script
Getting the errorlevel is not easy but reliable. You can pull backup history from msdb easily.
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
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.
I’ll try to figure out how to do it. Thanks
Pingback: The knack » Blog Archive » Tivoli Storage Manager and SQL backup