1. Please help support our sponsors by considering their products and services.
    Our sponsors enable us to maintain high-speed Internet connection and fast webservers.
    They support this free information and knowledge exchange forum service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions

Scripting question

Discussion in 'Microsoft SQL Server' started by Fattire, Dec 4, 2008.

  1. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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.)
     
  2.  
  3. toofarnorth

    toofarnorth ADSM.ORG Member

    Joined:
    Feb 1, 2005
    Messages:
    46
    Likes Received:
    0
    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?
     
  4. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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.
     
  5. moon-buddy

    moon-buddy ADSM.ORG Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,849
    Likes Received:
    363
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    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.
     
  6. JohanW

    JohanW ADSM.ORG Moderator

    Joined:
    Nov 12, 2006
    Messages:
    986
    Likes Received:
    14
    Occupation:
    sysadmin
    Location:
    Netherlands
    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.
     
  7. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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.
     
  8. moon-buddy

    moon-buddy ADSM.ORG Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,849
    Likes Received:
    363
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    Try this:

    %sql_dir%\"tdpsqlc backup NT10B"\CRYSTAL_DEVLPMT * full
     
  9. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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 *
     
  10. countstumpi

    countstumpi ADSM.ORG Member

    Joined:
    Feb 23, 2009
    Messages:
    7
    Likes Received:
    0
    Location:
    Germany
    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.
     
  11. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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?
     
  12. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    sorry, NT10B is also the server name as well.
     
  13. moon-buddy

    moon-buddy ADSM.ORG Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,849
    Likes Received:
    363
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    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: Mar 5, 2009
  14. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    no, the sql database name is "NT10B\CRYSTAL_DEVLPMT" (minus the quotes of course)
     
  15. moon-buddy

    moon-buddy ADSM.ORG Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,849
    Likes Received:
    363
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    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
     
  16. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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.
     
  17. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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...........
     
  18. moon-buddy

    moon-buddy ADSM.ORG Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,849
    Likes Received:
    363
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    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.
     
  19. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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
     
  20. moon-buddy

    moon-buddy ADSM.ORG Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,849
    Likes Received:
    363
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    Just wondering: is the database name really CRYSTAL_DEVLPMT, or is it a an alias?
     
  21. Fattire

    Fattire ADSM.ORG Member

    Joined:
    Jun 5, 2006
    Messages:
    210
    Likes Received:
    0
    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
     

Share This Page