[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQLTDP backups

Posted on 2009-04-30
Medium Priority
Last Modified: 2013-11-14
I am by no stretch of the imagination a SQL or TSM guru.  95% of the databases on the SQL server are read only.   I have a 2005 SQL server, that I use TDPSQL version to backup.  As the log shows it backs up every database every time.
I believe I want to do a full backup one day a week and differentials the rest, how do I accomplish this? Please provide and example.

Any and all help is greatly appreciated!!

Here is my cmd file.
rem ================================================== ================
rem sqldiff.cmd TDP for MS SQL command file
rem ================================================== ================

set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql


cd %sql_dir%

date < NUL > %sql_dir%\sql_diff_sched.log
time < NUL >> %sql_dir%\sql_diff_sched.log

rem ================================================== ================
rem Now call the command-line interface to do the backup:
rem ================================================== ================

%sql_dir%\tdpsqlc backup SUN*,ReportServer difffull /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sql_diff.log >> %sql_dir%\sql_diff_sched.log

Here is my sql_diff_log
04/28/2009 03:45:09 Request : DIFF BACKUP
04/28/2009 03:45:09 Database Input List : SUN*,ReportServer
04/28/2009 03:45:09 Group Input List : -
04/28/2009 03:45:09 File Input List : -
04/28/2009 03:45:09 Number of Buffers : 3
04/28/2009 03:45:09 Buffer Size : 1024
04/28/2009 03:45:09 Number of SQL Buffers : 0
04/28/2009 03:45:09 SQL Buffer Size : 1024
04/28/2009 03:45:09 Number of Stripes specified : 1
04/28/2009 03:45:09 Estimate : 20
04/28/2009 03:45:09 Truncate Log? : -
04/28/2009 03:45:09 Wait for Tape Mounts? : Yes
04/28/2009 03:45:09 TSM Options File : C:\Progra~1\Tivoli\TSM\TDPSql\dsm.opt
04/28/2009 03:45:09 TSM Nodename Override : -
04/28/2009 03:45:09 Sqlserver : abcd
04/28/2009 08:46:31 Total SQL backups selected: 99
04/28/2009 08:46:31 Total SQL backups attempted: 99
04/28/2009 08:46:31 Total SQL backups completed: 96
04/28/2009 08:46:31 Total SQL backups excluded: 0
04/28/2009 08:46:31 Throughput rate: 12,388.52 Kb/Sec
04/28/2009 08:46:31 Total bytes transferred: 229,131,335,168
04/28/2009 08:46:31 Elapsed processing time: 18,061.97 Secs
Question by:asrvwiz
  • 3
LVL 68

Accepted Solution

woolmilkporc earned 2000 total points
ID: 24293865
as far as I can see you are doing a 'differential' backup, which means that only those parts of the SQL database(s) get backed up, which have been modified since the last 'full' backup (along with the matching transaction logs).
Your last 'full' backup might have taken place a long time ago (or perhaps never happened),
If there was no full backup ever, a differential backup is just the same as a full backup.
If you did a full backup one time, only the changed parts since that full run are backed up. Attention - In case of a restore you imperatively need to restore the full backup along with the last differential one - so take care not to loose the media, and be sure that it's not defective!
Check the value SQLTDP outputs for 'bytes transferred' against your actual DB size to get a clue whether there was a full backup one time and/or how old it might be. You might as well issue
tdpsqlc query tsm *   to get a report about your backups.  
It would indeed be a better practice to proceed as you already mentioned in your Q - do a full backup weekly and differental backups at the remaining days. Use 'full' instead of 'diffull' to accomplish this (and adjust the names of the log files to reflect the fact that it's a full backup).
e.g. like this (for the weekly job, leave the job for the rest as is) -

rem ================================================== ================
rem sqlfull.cmd TDP for MS SQL command file
rem ================================================== ================
set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
cd %sql_dir%
date < NUL > %sql_dir%\sql_full_sched.log
time < NUL >> %sql_dir%\sql_full_sched.log
rem ================================================== ================
rem Now call the command-line interface to do the backup:
rem ================================================== ================
%sql_dir%\tdpsqlc backup SUN*,ReportServer full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sql_full.log >> %sql_dir%\sql_full_sched.log  
To learn more about tdpsqlc (especially the various 'query' options) - here is the guide -

Author Comment

ID: 24441316
Thanks for the information!  Our SQL databases are in "Simple" recovery model so I do NOT have to back up the log files correct?
LVL 68

Expert Comment

ID: 24441662
Hi again,

with the simple recovery model no recovery log backups are available, since the logs are truncated each time a checkpoint is issued for the database. Not only you do not have to backup them, you even can't.

tdpsql knows about  the recovery model used and will not try to backup any log in case of "simple".

Keep in mind that you will only be able to recover data to the most recent full database or differential backup. Transactions that have occurred between the last backup and the failure point are lost.

I, personally, would always recommend the full recovery model, at least with production-crticial databases.


LVL 68

Expert Comment

ID: 24441718
OK, I just re-read your Q and saw your statement "95% of the databases on the SQL server are read only"
In this case there might indeed be good reasons for using simple recovery.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The business world is becoming increasingly integrated with tech. It’s not just for a select few anymore — but what about if you have a small business? It may be easier than you think to integrate technology into your small business, and it’s likely…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question