• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

Scripting question

Fattire

ADSM.ORG Member
#1
Installing first time, tdsql 5.5.1, on windows 2003 sp2 for sql2005. I want to keep simple as this is new to me. I want to do a once weekly full backup with daily incrementals the rest of the week. Do i use the same sqlfull.smp script to accomplish this? I'm not much of a scripter so any help would be appreciated.
Also, if there are 2 databases on this one server, can I use the same script? yes I've read through installation/users guide and the redbook for tdsql 5.5. thanks for any help.
(TSM server 5.5.)
 

toofarnorth

ADSM.ORG Member
#2
I have two different scripts (as sqlfull.bat and sqlincr.bat) on our servers. The text from one of those sqlfull.bat files is:


*******************************************************************
@ECHO OFF
rem ==================================================================
rem sqlfull.smp sample command file
rem
rem Sample command file containing commands to do a scheduled full
rem backup of all SQL databases to an IBM Tivoli Storage Manager
rem server.
rem
rem This file is meant to be executed by the IBM Tivoli Storage
rem Manager central scheduler in response to a defined schedule on
rem the IBM Tivoli Storage Manager server.
rem
rem ==================================================================

rem ==================================================================
rem Replace "C:" with the drive where Data Protection for SQL
rem is installed. Update the directory to match the installation
rem directory that you chose when you installed the product.
rem ==================================================================

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

C:

cd %sql_dir%

rem ==================================================================
rem The two lines below put a date/time stamp in a log file for you.
rem Note: You can change "sqlsched.log" to whatever you prefer in
rem lines below.
rem ==================================================================

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

rem ==================================================================
rem Now call the command-line interface to do the backup:
rem
rem Replace "srvrname" with the name of the options file name you
rem plan to use.
rem
rem If SQL authentication is being used and the SQL login settings have
rem not been stored via the GUI, you must also specify the /sqluser and
rem /sqlpassword options on the command below.
rem
rem In this example, we use the '*' to back up all of the databases
rem on the SQL server. Note that database 'tempdb' will not
rem be backed up.
rem
rem Note: You can change "sqlsched.log" and "sqlfull.log" to
rem whatever you prefer.
rem ==================================================================

%sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

***********************************************************************


--In this instance, "backup * full" is used to just backup every database on the server (it's easier than naming them individually, as there are many). The dsm.opt files and tdpsql.cfg files are pretty generic -- the node name in the dsm.opt file, and that's it. On another server, rather than have "backup *", we have a separate "Backup <SLQSERVERNAME> full" line for each server instance, because there are a couple we have to specifically exclude. If you don't have to exclude something, then "backup * full" is easiest.

The incremental backup sqlincr.bat is almost identical to the script above, except that it writes to different logs, as well as the standard sqlsched.log, and we actually specify the SQL servers to be backed up here, too:


*******************************************************************
[as script above]...
...

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


%sql_dir%\tdpsqlc backup <SQLSERVER1> log /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlincr.log >> %sql_dir%\sqlsched.log
%sql_dir%\tdpsqlc backup <SQLSERVER2> log /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlincr.log >> %sql_dir%\sqlsched.log
%sql_dir%\tdpsqlc backup <SQLSERVER3> log /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlincr.log >> %sql_dir%\sqlsched.log

*********************************************************************


...Obviously, when you are replacing the <SQLSERVERx> with your SQL server names, you remove the '<' and '>', as well.

Anyway, with these scripts set up, basically I just scheduled a weekly COMMAND backup that runs once or twice a week and points at the sqlfull.bat file, and a nightly COMMAND backup that points at the sqlincr.bat file.

Does that help?
 

Fattire

ADSM.ORG Member
#3
Yes, thanks, helps alot. One question,,,I Have 2 sql instances(1 server), so I create 2 nodes on TSM for them, no prob. How do i set up the schedules to back each one up on a daily incremental basis AND once a week? I mean, each node can only pull in one schedule? Or am I setting up 2 schedules for each instance on TSM(daily and weekly) and setting up 4 scheduler services on the SQL server? each service grabbing its own appropriate schedule for each server instance.
 

moon-buddy

ADSM.ORG Moderator
#4
You can use one TDP for SQL service to backup the two SQL instances assuming that it is OK to backup the SQL instance at the same time, i.e., same time for incremental and full.

All you need to do is create two client schedules on the TSM Server: one calling the incremental backup command, and the other the full backup command.
 

JohanW

ADSM.ORG Moderator
#5
[snip]

On another server, rather than have "backup *", we have a separate "Backup <SLQSERVERNAME> full" line for each server instance, because there are a couple we have to specifically exclude. If you don't have to exclude something, then "backup * full" is easiest.

[snip]
And if you do have to exclude something, then "backup * full" is also easiest, because you can do excludes on the database level very easily. And you don't miss the new one until you've remembered to explicitly include it in the backup.

The syntax for excludes is in the manual, but if it's convenient for you, I can look up the syntax on one of our production machines.
 

Fattire

ADSM.ORG Member
#6
in my command.bat file I'm trying to point to a particular server instance (there are 2) and TSM is not liking my syntax:
%sql_dir%\tdpsqlc backup NT10B\CRYSTAL_DEVLPMT * full /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log
%sql_dir%\tdpsqlc backup NT10B\CRYSTAL_DEVLPMT * log /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log

Is the problem with the instance name NT10B\\CRYSTAL_DEVELPMT? the backslash? tried it with 2 backslashes and it still bombs.

Also, I read where I have to create another tdpsql.cfg file. it looks like the other one though for the other instance. Am I to change that SQLserver parm to match the name of the instance NT10\CRYSTAL_DEVELPMT?

LASTPRUNEDate 03/04/2009 12:14:22
SQLAUTHentication INTegrated
SQLSERVer NT10B
FROMSQLserver NT10B
MOUNTWaitfordata Yes
BACKUPMethod LEGACY
DIFFESTimate 20
BUFFers 3
BUFFERSIze 1024
STRIPes 1
SQLBUFFers 0
SQLBUFFERSIze 1024
LOGPrune 60
LANGuage ENU

Thanks for help. I'm almost there I think.
 

Fattire

ADSM.ORG Member
#8
tried that, no go. also tried with quotes around instance name,

%sql_dir%\tdpsqlc backup "NT10B\CRYSTAL_DEVLPMT" * full /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log

keeps giving me:
ACO0102E Invalid command: backup NT10B\CRYSTAL_DEVLPMT *
 

countstumpi

ADSM.ORG Member
#9
Is NT10B your Servername or the name of the instance? I think the "*" is false in your statement. Cause you define NT10B\CRYSTAL_DEVLPMT a database and then you can't use the wildcard * anymore.
 

Fattire

ADSM.ORG Member
#10
ok, statement now looks like this, took out quotes and that "*" (which made sense)

%sql_dir%\tdpsqlc backup NT10B\CRYSTAL_DEVLPMT full /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log
%sql_dir%\tdpsqlc backup NT10B\CRYSTAL_DEVLPMT log /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log

Which got me further somewhat as far as error messages:
ACO5057I The C:\Progra~1\Tivoli\TSM\TDPSql\sqlfull_crystal.log log file was pruned successfully.

Connecting to SQL Server, please wait...

ACO5460E Database NT10B\CRYSTAL_DEVLPMT doesn't exist or isn't valid.
ACO5552I No matches were found for the criteria specified.

when I click on the database instance name in enterprise manager and go to properties the name I have is the name it says. Is that backslash causing the problem?
 

moon-buddy

ADSM.ORG Moderator
#12
Is "tdpsqlc backup NT10B" the name of directory? If it is, strange way of creating a name.

Try:

%sql_dir%\"tdpsqlc backup NT10B\CRYSTAL_DEVLPMT" * full /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log
 
Last edited:

moon-buddy

ADSM.ORG Moderator
#14
OK, I am bad today! I seem not to read properly. Remove NT10B, and the command should be:

%sql_dir%\tdpsqlc backup CRYSTAL_DEVLPMT FULL /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal.log
 

Fattire

ADSM.ORG Member
#15
ok, thanks Ed. I'll try that after our morning admin tasks are done. On another note, docs say to create a seperate config file for the other instance, in this case for CRYSTAL_DEVLMPT,(which i've done) My question, when I run a scheduled backup on this db, how does TSM know to use that particular config file? For example, when i run the dpsql gui, it always defaults to the original dpsql.cfg i set up first.
 

Fattire

ADSM.ORG Member
#16
still not getting CRYSTAL_DEVLPMT to backup.
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 5, Release 5, Level 1.0
(C) Copyright IBM Corporation 1997, 2008. All rights reserved.

ACO5057I The C:\Progra~1\Tivoli\TSM\TDPSql\sqlfull_crystal.log log file was pruned successfully.

Connecting to SQL Server, please wait...

ACO5460E Database CRYSTAL_DEVLPMT doesn't exist or isn't valid.
ACO5552I No matches were found for the criteria specified.

Help...........
 

moon-buddy

ADSM.ORG Moderator
#17
Try this:

%sql_dir%\tdpsqlc backup * full /sqlserver=<the_SQL_Server_name> /tsmoptfile=%sql_dir%\<your_dsm.opt> /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

And see if it backed up ALL of the defined databases. If CRYSTAL_DEVLPMT has been defined properly, you should see it backed up.
 

Fattire

ADSM.ORG Member
#18
we used that command originally and here is the output. After I created the other config file for CRYSTAL_DEVLPMT I tried this command again but still shows the same. To be honest, it doesn't really show which instance its backing up, but when looking at gui to see whats available to restore, it is the first instance, called NT10B
This is from the sqlsched.log

Connecting to SQL Server, please wait...


Starting SQL database backup...

Connecting to TSM Server as node 'NT10B_DPSQL'...

Beginning full backup for database master, 1 of 5.
Full: 3 Read: 3145728 Written: 0 Rate: 0.00 Kb/Sec
Full: 3 Read: 3145728 Written: 0 Rate: 0.00 Kb/Sec
Full: 3 Read: 3145728 Written: 0 Rate: 0.00 Kb/Sec
Full: 3 Read: 3145728 Written: 0 Rate: 0.00 Kb/Sec
Full: 3 Read: 3145728 Written: 0 Rate: 0.00 Kb/Sec

Waiting for TSM server.........................................................................
Full: 3 Read: 10485760 Written: 7340032 Rate: 74.44 Kb/Sec
Full: 0 Read: 16210432 Written: 16210432 Rate: 162.30 Kb/Sec
Full: 0 Read: 16210432 Written: 16210432 Rate: 160.24 Kb/Sec
Full: 0 Read: 16212736 Written: 16212736 Rate: 158.26 Kb/Sec
Full: 0 Read: 16212736 Written: 16212736 Rate: 156.50 Kb/Sec

Backup of master completed successfully.

Beginning full backup for database model, 2 of 5.
Full: 0 Read: 735744 Written: 735744 Rate: 576.65 Kb/Sec
Full: 0 Read: 738048 Written: 738048 Rate: 288.76 Kb/Sec
Full: 0 Read: 738048 Written: 738048 Rate: 192.41 Kb/Sec
Full: 0 Read: 738048 Written: 738048 Rate: 160.31 Kb/Sec

Backup of model completed successfully.

Beginning full backup for database msdb, 3 of 5.
Full: 0 Read: 11687424 Written: 11687424 Rate: 9,036.82 Kb/Sec
Full: 0 Read: 11687424 Written: 11687424 Rate: 4,541.78 Kb/Sec
Full: 0 Read: 11689728 Written: 11689728 Rate: 3,033.68 Kb/Sec
Full: 0 Read: 11689728 Written: 11689728 Rate: 2,461.35 Kb/Sec

Backup of msdb completed successfully.

Beginning full backup for database Northwind, 4 of 5.
Full: 0 Read: 2703872 Written: 2703872 Rate: 2,117.48 Kb/Sec
Full: 0 Read: 2706176 Written: 2706176 Rate: 1,058.37 Kb/Sec
Full: 0 Read: 2706176 Written: 2706176 Rate: 705.30 Kb/Sec
Full: 0 Read: 2706176 Written: 2706176 Rate: 579.68 Kb/Sec

Backup of Northwind completed successfully.

Beginning full backup for database pubs, 5 of 5.
Full: 0 Read: 1392128 Written: 1392128 Rate: 1,090.22 Kb/Sec
Full: 0 Read: 1394432 Written: 1394432 Rate: 545.35 Kb/Sec
Full: 0 Read: 1394432 Written: 1394432 Rate: 363.42 Kb/Sec
Full: 0 Read: 1394432 Written: 1394432 Rate: 298.69 Kb/Sec

Backup of pubs completed successfully.

Inactivating log backup model\20090305230611\000005F8

Total SQL backups selected: 5
Total SQL backups attempted: 5
Total SQL backups completed: 5
Total SQL backups excluded: 0
Total SQL backups inactivated: 1

Throughput rate: 267.74 Kb/Sec
Total bytes transferred: 32,741,120
Elapsed processing time: 119.42 Secs



IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 5, Release 5, Level 1.0
(C) Copyright IBM Corporation 1997, 2008. All rights reserved.

ACO5057I The C:\Progra~1\Tivoli\TSM\TDPSql\sqlfull.log log file was pruned successfully.

Connecting to SQL Server, please wait...


Starting SQL database backup...

Connecting to TSM Server as node 'NT10B_DPSQL'...

Beginning log backup for database model, 1 of 1.
Full: 0 Read: 80640 Written: 80640 Rate: 63.30 Kb/Sec
Full: 0 Read: 80640 Written: 80640 Rate: 31.60 Kb/Sec
Full: 0 Read: 80640 Written: 80640 Rate: 24.29 Kb/Sec

Backup of model completed successfully.


Total SQL backups selected: 5
Total SQL backups attempted: 1
Total SQL backups completed: 1
Total SQL backups excluded: 4

Throughput rate: 24.28 Kb/Sec
Total bytes transferred: 80,640
Elapsed processing time: 3.24 Secs
 

Fattire

ADSM.ORG Member
#20
thanks for all your help. I did get it to finally work with this script:

%sql_dir%\tdpsqlc backup * full /configfile=tdpsql_crystal.cfg /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal_sun.log
%sql_dir%\tdpsqlc backup * log /configfile=tdpsql_crystal.cfg /tsmoptfile=%sql_dir%\dsm_crystal.opt /logfile=%sql_dir%\sqlfull_crystal.log >> %sql_dir%\sqlsched_crystal_sun.log

%sql_dir%\tdpsqlc backup * full /configfile=tdpsql.cfg /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched_sun.log
%sql_dir%\tdpsqlc backup * log /configfile=tdpsql.cfg /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched_sun.log
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 9 20.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 23 52.3%
  • Let's be formal and just say Spectrum Protect

    Votes: 8 18.2%
  • Other (please comement)

    Votes: 4 9.1%

Forum statistics

Threads
31,055
Messages
132,235
Members
21,274
Latest member
ctauber
Top