backup filename

• trans52

  SSC Enthusiast

  Points: 112

  More actions

  July 28, 2003 at 9:59 am

  #106036

  Hi guys,

  Can you please point me to the correct solution of my problem:

  I have a backup stored procedure something like:

  SET NOCOUNT ON

  --

  -- Build the device and backup file names

  --

  SET @device = LTRIM(@server) + '_' + LTRIM(@db) + '_' + convert(varchar(20), getdate(), 112) + '_BACKUP'

  SET @devfile = LTRIM(@devloc) + @device + '.BAK'

  --

  -- Backup the database

  --

  BACKUP DATABASE @db TO @device WITH INIT, STATS=10

  which i run everyday as a SQL job and creates a backup for master, msdb and user defined databases.

  Now our Sys. admin wants to run a perl script to execute sql job and when it's done to perform backup to the tape.

  The problem is that when he runs the perl script he wants the backup filename return to him and then his script waiting

  until the backup ( he checks physical file) is done and perform tape backup immediatelly.

  How can i return the backup filename to him?

  Thank you

• dgermundson

  Hall of Fame

  Points: 3175

  More actions

  July 28, 2003 at 11:39 am

  #467335

  Did you try using an output parameter in your stored procedure?

  From BOL:

  CREATE PROCEDURE get_sales_for_title

  @title varchar(80), -- This is the input parameter.

  @ytd_sales int OUTPUT -- This is the output parameter.

  AS

  -- Get the sales for the specified title and

  -- assign it to the output parameter.

  SELECT @ytd_sales = ytd_sales

  FROM titles

  WHERE title = @title

  RETURN

  GO

  Darren

  
  Darren