backup database with scheduled job

• Sílvia Ana Pereira Martins

  SSC Rookie

  Points: 29

  More actions

  June 9, 2004 at 5:43 am

  #152178

  hi.

  I'm trying to backup a database with a scheduled job. In the name of the backup file, i was trying to put the date when the job is done.

  command in job step:

  backup database xyz to disk='C:\bk\xyz_'+ convert(varchar(10),getdate(),112) + '.bak'

  the error i get is:

  Msg 170, Level 15, State 1, Server XPTO, Line 1

  Line 1: Incorrect syntax near '+'.

  Can i do this concatenation? And am i doing the right thing?

  Thank's.

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  June 9, 2004 at 6:16 am

  #509623

  What you will probably end up doing is building a variable that has all the information in it and then use xp_cmdshell to initiate it:

  DECLARE @Var NVARCHAR(255)

  SET @Var = 'backup database xyz to disk=''C:\bk\xyz_' + convert(varchar(10),getdate(),112) + '.bak'''

  And then initiate it by

  EXEC master..xp_cmdshell @Var

  This may not be exact syntax but hopefully is close enough 

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Arvind-57714

  SSCrazy

  Points: 2529

  More actions

  June 9, 2004 at 6:17 am

  #509624

  Hi,

  Try this instead..this shud work fine!

  declare @nam varchar(100)

  set @nam = 'C:\xyz_'+ convert(varchar(10),getdate(),112) + '.bak'

  backup database xyz to disk = @nam

  Cheers

  
  Arvind

• Sílvia Ana Pereira Martins

  SSC Rookie

  Points: 29

  More actions

  June 9, 2004 at 8:28 am

  #509653

  hi.

  thanks for your post, but i tryed this in command prompt and it worked. but it worked executing those 3 commands separatly.

  I also executed the 3 steps in the same job, but in the end, the last step didn't recognise the variable @nam, saying "Must declare the variable '@nam'. [SQLSTATE 42000] (Error 137)"

  i think this happens because the variable @nam "get lost in the way".

  How may i put all this 3 steps in just one?

  Thank you.

• Arvind-57714

  SSCrazy

  Points: 2529

  More actions

  June 9, 2004 at 8:45 am

  #509659

  I dont understand what you are trying to do. Simply try this.

  Put the 3 lines in a new step in a job under master db selection.

  I have scripted a similar job for you. Try this ...just execute in QA.

  ******************************************

  BEGIN TRANSACTION           

    DECLARE @JobID BINARY(16) 

    DECLARE @ReturnCode INT   

    SELECT @ReturnCode = 0    

  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1

    EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'

    -- Delete the job with the same name (if it exists)

    SELECT @JobID = job_id    

    FROM   msdb.dbo.sysjobs   

    WHERE (name = N'Back')      

    IF (@JobID IS NOT NULL)   

    BEGIN 

    -- Check if the job is a multi-server job 

    IF (EXISTS (SELECT  *

                FROM    msdb.dbo.sysjobservers

                WHERE   (job_id = @JobID) AND (server_id <> 0)))

    BEGIN

      -- There is, so abort the script

      RAISERROR (N'Unable to import job ''Back'' since there is already a multi-server job with this name.', 16, 1)

      GOTO QuitWithRollback 

    END

    ELSE

      -- Delete the [local] job

      EXECUTE msdb.dbo.sp_delete_job @job_name = N'Back'

      SELECT @JobID = NULL

    END

  BEGIN

    -- Add the job

    EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Back', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    -- Add the job steps

    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step1', @command = N'declare @nam varchar(100)

  set @nam = ''C:\xyz_''+ convert(varchar(10),getdate(),112) + ''.bak''

  backup database xyz to disk = @nam

  ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    -- Add the Target Servers

    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  END

  COMMIT TRANSACTION         

  GOTO   EndSave             

  QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

  EndSave:

  ************************************************

  Dont forget to change the DB and Server Name in the Code above.

  Lemme know if this helps.

  Cheers!

  
  Arvind

• Sílvia Ana Pereira Martins

  SSC Rookie

  Points: 29

  More actions

  June 9, 2004 at 9:13 am

  #509673

  hi, again.

  Thank you very mutch for your help!

  I solved my problem with the script you posted.

  Thanks very mutch again!