Scripting question

[Fattire]

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]

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]

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]

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]

[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]

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.

 

[moon-buddy]

Try this:

%sql_dir%\"tdpsqlc backup NT10B"\CRYSTAL_DEVLPMT * full

 

[Fattire]

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]

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]

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?

 

[Fattire]

sorry, NT10B is also the server name as well.

 

[moon-buddy]

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

 

[Fattire]

no, the sql database name is "NT10B\CRYSTAL_DEVLPMT" (minus the quotes of course)

 

[moon-buddy]

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]

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]

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]

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]

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

 

[moon-buddy]

Just wondering: is the database name really CRYSTAL_DEVLPMT, or is it a an alias?

 

[Fattire]

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