/* Job Scheduler Template */
/*
** Messages for stored procedures in this file
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19123, "%1! value of %2! is invalid."
** 19124, "Backup for database %1! on %2! failed."
** 19125, "Database %1! successfully backed up on %2!."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
-- Remove the sp_jst_dump_db_to_disk if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_db_to_disk'
             and type = 'P'
             and (sysstat & 7) =4 )
    drop proc sp_jst_dump_db_to_disk
go
/* 
** sp_jst_dump_db_to_disk
** Version: 1.0
** 
** This stored procedure forms the dump filename and dump command
** for the database to be dumped.  It then executes the dump command.
**
** This procedure was developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running a job created from the "Sybase Backup Database
** Template"
** 
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from sp_jst_dump_dbs_to_disk
**
** Parameter definitions
** @db_name        - names of database that should be dumped
** @dump_location  - device name or path to location where dump file
**                   will be created.
** @dump_filename  - flag indicating if server name should be included
** @start_stripe   - the first stripping value
** @end_stripe     - the last stripping value
** @compress_flag  - indicates if compression should be used
** @compress_value - indicates compression level
*/
create procedure sp_jst_dump_db_to_disk( @db_name varchar(30),
                                 @dump_location varchar(128),
                                 @dump_filename varchar(1000),
                                 @start_stripe int,
                                 @end_stripe int,
                                 @compress_flag varchar(5),
                                 @compress_value int ) 
as
begin

    print 'sp_jst_dump_db_to_disk(db_name = %1!, dump_location = %2!, dump_filename = %3!, start_stripe = %4!, end_stripe = %5!, @compress_flag = %6!, 
           compress_value = %7! )', @db_name, @dump_location, 
           @dump_filename, @start_stripe, @end_stripe, @compress_flag, 
           @compress_value

    declare @cntr int
          , @tmp_filename varchar(128)
          , @dmp_params varchar(500)
          , @err int
          , @msgno int
          , @sp_name varchar(30)
          , @path_separator char(1)
          , @str_len int
          , @end_char char(1)

    select @cntr = @start_stripe
         , @sp_name = 'sp_jst_dump_db_to_disk'

    --  use dump location if provided
    select @dump_location = rtrim(ltrim(@dump_location))
    if( @dump_location != NULL )
      begin

        -- make sure that the dump_location has a / or \ at the end
        -- question: what do we do if no path separator in dump_location
        select @str_len = char_length( @dump_location )
        select @end_char = substring( @dump_location, @str_len, @str_len )
        if( @end_char != '/' and @end_char != '\' )
          begin
            declare @pos int
            select @pos = charindex( '/', @dump_location )
            if( @pos > 0 )
              begin
                select @dump_location = @dump_location + '/'
              end
            else
              begin
                select @pos = charindex( '\', @dump_location )
                if( @pos > 0 )
                    select @dump_location = @dump_location + '\'
              end
          end
      end

    -- all values are expected to be filled in by previous
    -- stored procedures
    select @dmp_params = " " + @db_name + " to " 
 
    if( @cntr > 0 )
      begin
        while( @cntr <= @end_stripe )
          begin
            select @tmp_filename = @dump_filename + "_s" + 
                                convert(varchar(2), @cntr) + ".dmp"
            if( @cntr > 1)
                select @dmp_params = @dmp_params + "stripe on " 

            if( @compress_flag = 'true' )
              begin
                  select @dmp_params = @dmp_params + '"' + "compress::" +
                                 convert(char(1),@compress_value) + "::" 
                  if( @dump_location != NULL )
                    begin
                      select @dmp_params = @dmp_params  
                                           + @dump_location  
                                           + @tmp_filename + '" '
                    end
                  else
                    begin
                      select @dmp_params = @dmp_params + @tmp_filename + '" '
                    end
              end
            else  --no compression
              begin
                  if( @dump_location != NULL )
                    begin
                      select @dmp_params = @dmp_params + '"' 
                                           + @dump_location  
                                           + @tmp_filename + '" '
                    end
                  else
                    begin
                  select @dmp_params = @dmp_params + '"' 
                                       + @tmp_filename + '" '
                    end
              end
            select @cntr = @cntr+1
          end --while
      end -- if striping
    else
      begin
        if( @compress_flag = 'true' )
          begin
              select @dmp_params = @dmp_params + '"' + "compress::" +
                      convert(char(1),@compress_value) + "::" 
              if( @dump_location != NULL )
                begin
                  select @dmp_params = @dmp_params  
                               + @dump_location 
                               + @dump_filename 
                               + ".dmp" + '"'
                end
              else
                begin
                  select @dmp_params = @dmp_params 
                                       + @dump_filename 
                                       + ".dmp" + '"'
                end
          end
        else
          begin
              select @dmp_params = @dmp_params + '"' 
              if( @dump_location != NULL )
                begin
                  select @dmp_params = @dmp_params  
                                       + @dump_location 
                                       + @dump_filename 
                                       + ".dmp" + '"'
                end
              else
                begin
                  select @dmp_params = @dmp_params 
                                       + @dump_filename 
                                       + ".dmp" + '"'
                end
          end
      end

    print 'run: dump database %1!', @dmp_params
    exec( "dump database" + @dmp_params )
    select @err = @@error

    --Log success or failure for each db
    declare @dump_time varchar(8)
    select @dump_time = convert( char(8), getdate(), 3 )
    if( @err != 0 )
      begin
          raiserror 19124, @db_name, @dump_time
          return -@@error
      end
    else
      begin
          raiserror 19125, @db_name, @dump_time
      end
 
    return 0
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_db_to_disk'
             and type = 'P'
             and (sysstat & 7) =4 )
begin
    print 'sp_jst_dump_db_to_disk has been created'
    grant execute on sp_jst_dump_db_to_disk to public
end
go

-- Remove the sp_jst_dump_dbs_to_disk if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_dbs_to_disk'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_dump_dbs_to_disk
go
print 'Creating Stored Procedures for Sybase Backup Database to Disk Template'
go
/* 
** sp_jst_dump_dbs_to_disk
** Version: 3.1
** 
** This stored procedure runs the dump database command using the
** input provided to determine the form of the dump command to run.
** It ensures the required data is provided and determines which
** databases are to be dumped.  It then calls the sp_jst_dump_db_to_disk SP 
** to form the dump filename and dump command and actually dump the 
** database.
** 
** NOTE:
** This stored procedure is limited to dumping databases to disk; tape
** dumps are not handled.
**
** This procedure was developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running a job created from the "Sybase Backup Database
** Template"
** 
** This stored proc is the called at job execution.  The Job Scheduler
** GUI will gather the values for each parameter and form the job
** SQL when a job is created from the associated template.
**
** Parameter definitions
** @db_names       - one or more names of databases that should be dumped
** @dump_location  - path to location where dump file will be created.
** @use_srvr_name  - flag indicating if server name should be included
**                   in the dump filename;  0=true, 1=false
** @date_fmt       - indicates what date format to use in dump filename
** @stripe_cnt     - number of stripes to be created when dumping.
** @compress_value - indicates compression level
*/
create procedure sp_jst_dump_dbs_to_disk ( @db_names varchar(500),
                                           @dump_location varchar(128)=NULL,
                                           @use_srvr_name int = 1,
                                           @date_fmt int=0,
                                           @stripe_cnt int = 0,
                                           @compress_value int = 0)
as
begin

    print 'sp_jst_dump_dbs_to_disk(db_names = %1!, dump_location = %2!, 
          use_srvr_name = %3!, date_fmt = %4!, stripe_cnt = %5!, 
          compress_value = %6! )', 
          @db_names, @dump_location, @use_srvr_name, @date_fmt, 
          @stripe_cnt, @compress_value  

    declare @dump_filename varchar(225)
          , @start_stripe int
          , @end_stripe int
          , @ase_version_ok int
          , @tmp_string varchar(128)
          , @compress_flag varchar(5)
          , @date_str varchar(25)
          , @msgno int
          , @err int
          , @sp_name varchar(30)
          , @status int

    select @ase_version_ok=1
         , @sp_name = 'sp_jst_dump_dbs_to_disk'  
         , @status=0

    -- check for required input values
    --  must have at least one DB name
    select @db_names = rtrim(ltrim(@db_names))
    if( @db_names = NULL )
      begin
          raiserror 19048, @sp_name, 'database name'
          return -@@error
      end

    --  check for valid compession level, 0 - 9
    --  if value is NULL set to 0, no compression
    if( @compress_value = NULL )
        select @compress_value = 0

    if( @compress_value < 0 or @compress_value > 9 )
      begin
          raiserror 19123, 'Compression', @compress_value  
          return -@@error
      end

    --  check for "all" option in database names
    if( lower(@db_names) = "all" )
      begin
        -- we need to dump all databases on the server except model &
        -- tempdb
        -- call method to get all db_names
        exec @err = sp_jst_database_names @db_names out  
        if( @err < 0 )
          return @err
      end

    --determine if user selected compression, if so check db version to 
    --see if it is available
    exec @ase_version_ok = sp_jst_valid_ase_version '12.5'

    if( (@ase_version_ok=0) and (@compress_value > 0) )
        select @compress_flag = 'true' 
    else
        select @compress_flag = 'false' 

    --check for striping, if specified, create start and end values
    if( @stripe_cnt = NULL )
        select @stripe_cnt = 0
    if( @stripe_cnt > 0 )
      begin
        select @start_stripe = 1
        select @end_stripe = @stripe_cnt
      end
    else
      begin
        select @start_stripe = 0
        select @end_stripe = 0
      end

    --create base output file names for each db specified
    --this name will be used by sp_jst_dump_db_to_disk to create
    --actual name if stripping is requested.
    declare @base_names varchar(500)
          , @db_name varchar(200)
          , @end_pos int
          , @cur_pos int
          , @len int

    select @base_names = @db_names 

    select @len = isnull(datalength(@base_names),0), @cur_pos=1

    -- false = 1, true = 0
    if( @use_srvr_name = NULL )
        select @use_srvr_name = 1

    if( @date_fmt = NULL )
        select @date_fmt = 0
    if( @date_fmt > 0 )
        exec @err = sp_jst_get_datestr @date_fmt, @date_str out
    if( @err = -1 )
        return @err
    if( @date_str = NULL )
        select @date_fmt = 0  

    --loop through each db specified
    while  @len > 0
      begin
        --get the filename, only, next or last
        select @end_pos = patindex('%[,]%', @base_names )
        if @end_pos <= 0
          begin
            -- only or last db_names
            select @db_name = ltrim(@base_names)
            select @dump_filename = @db_name 
            select @len = 0
          end
        else
          begin
            -- get the next name
            select @db_name = substring(@base_names,1,@end_pos-1)
            select @db_name = ltrim(rtrim(@db_name))
            select @dump_filename = @db_name 
            -- remove the db we are currently processing
            select @base_names = stuff(@base_names,1,@end_pos,NULL)
            select @len = isnull(datalength(ltrim(@base_names)),0)
          end

        -- build the rest of the dump filename
        -- include the server name if use_srvr_name = 0
        if @use_srvr_name = 0
          begin
            select @dump_filename = @dump_filename + @@servername
          end
        if @date_fmt > 0
          begin
            select @dump_filename = @dump_filename + @date_str 
          end

        --dump the current db using the dump command
        exec @err = sp_jst_dump_db_to_disk @db_name, 
                                           @dump_location, 
                                           @dump_filename,
                                           @start_stripe,
                                           @end_stripe,
                                           @compress_flag,
                                           @compress_value  
        if( @err < 0)
            select @status = @err
        
      end --end while

    return @status
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_dbs_to_disk'
             and type = 'P'
             and (sysstat & 7) =4 )
begin
    print 'sp_jst_dump_dbs_to_disk has been created'
    grant execute on sp_jst_dump_dbs_to_disk to public
end
go

