Hi to all,

Im using the sample provide in this link
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q241397&ID=KB;EN-US;Q241397
for backupping a database via a job.

I want to make the filename dynamic, i.e. create it using the current system
date time to obtain a filename in the form 'YYYYMMDD.bak'. I have written
the code that do that and it works fine in a stored procedure ***inside the
database*** I want to backup

But I get problem when I apply the code that compose this string in top of
the script described in the link above,
I get an error message:

message 170, level 15, state 1 server XXX, line 7
Line 7: Syntax error near '+' (sorry I'm translating from an Italian error
message, I don't know the exact english one)

And then
Message 22022, level 16, state 1
SQLAgent error: cannot complete job because the job doesn't include steps.

I repeat, all works fine in the stored procedure of the database I want to
backup. Is simply the script for the jobs that mess it up.

Any Idea?

If urge I will post the code that fire error

Thanks a lot

S.

If you post the code (or a link to where we can read the code, we can probably spot the problem).

Show quoteHide quote

probably spot the problem).
Here The code pal:

USE msdb
declare @BASEPATH  VARCHAR(100)
declare @DATA  datetime
declare @FILE  varchar(100)
SET @BASEPATH = "C:\"
SET @DATA = GETDATE()
set @FILE = '@BASEPATH + (CAST(year(@data) as VARCHAR(4))) + '_' +
(CAST(month(@data) as VARCHAR(2))) + '_' + (CAST(day(@data) as VARCHAR(2)))
+ '.bak'
--PRINT @file

EXEC sp_add_job @job_name = 'myTestBackupJob',
    @enabled = 1,
    @description = 'myTestBackupJob',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_level_netsend =2,
    @notify_level_page = 2
--  @notify_email_operator_name = 'email name'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb Data',
    @subsystem = 'TSQL',
    @command = 'BACKUP DATABASE DB_EAL TO DISK =' + @FILE,
    @on_success_action = 3,
    @retry_attempts = 5,
    @retry_interval = 5
go

-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @job_name = 'myTestBackupJob', @server_name =
N'AL-SVILUPPO'

-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @job_name = 'myTestBackupJob'




http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q241397&ID=KB;EN-US;Q241397
Show quoteHide quote

DevX (noth***@yet.com) writes:
And just as you can't have expressions for the file name in the
BACKUP command, neither can you have expressions for parameters to
stored procedure. You can only pass constants and variables. So you
need to put the complete SQL command in a variable and pass that.

Ok, thanks
I don't get anymore error messages, but also I don't get the backup file.
See here the code (omitted the parts above and below):

.....
-- Add job step (backup data).
USE msdb
declare @BASEPATH  VARCHAR(100)
declare @DATA  datetime
declare @FILE  varchar(100)
SET @BASEPATH = 'BACKUP DATABASE XXX TO DISK = C:\'
SET @DATA = GETDATE()
set @FILE = @BASEPATH + (CAST(year(@data) as VARCHAR(4))) + '_' +
(CAST(month(@data) as VARCHAR(2))) + '_' + (CAST(day(@data) as VARCHAR(2)))
+ '.bak'
print @FILE -- I get the corret printing here
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb Data',
    @subsystem = 'TSQL',
    @command =  @FILE,
    @on_success_action = 3,
    @retry_attempts = 5,
    @retry_interval = 5
go
.....


Show quoteHide quote

DevX (noth***@yet.com) writes:
Did you check the job history for errors? There should be errors, because:

The correct syntax is:

   BACKUP DATABASE xxx TO DISK = 'C:\2006_08_03.bak'

You have failed to quote the filename.

By the say, you can use datename() to make the code slightly less verbose.
And if you say convert(char(10), getdate(), 120) it's even terser, although
you get the dates in a slighly different format.

DevX (noth***@yet.com) writes:
Show quoteHide quoteSounds like you are trying something  like:

  BACKUP DATABASE db TO DISK =
      'C:\temp\' + convert(char(8), getdate(), 112) + 'bak'

That want fly. You need to use a varible:

   SELECT @filename = ....
   BACKUP DATABASE db TO DISK = @filename

I use a Variable exactly like your post. But I have trouble. See my source
code in the reply to Tibor I posted.

Thakns

Show quoteHide quote

How to use "Not Exists" ?
Generating multiple rows with xml data column
delete rows timeout
Selecting TOP 1 of each group
Two blanks to be changed to one