1 Reply Latest reply on Jul 1, 2020 8:57 AM by Bentze Perlmutter

    Need script to  backup using batch script in SQL SERVER. COuld you please proivide me a script which i can deploy in CONTORL-M .   So that once backup completes has to give message to the contorl-M operator about success/fail (0 or 1) .  Then then can cal

    Vijay Ramalingegowda
      Share This:

      Need script to  backup using batch script in SQL SERVER. COuld you please proivide me a script which i can deploy in CONTORL-M .

      So that once backup completes has to give message to the contorl-M operator about success/fail (0 or 1) .  Then Operator can call DBA based on status.

       

       

      Some how my current script is running from SQL server Agent sucessfully but when I  inform COntrol-M operator to run from his end . It just completes as SUCESS without running jobs:

       

      Please suggest and help us to get required script which I can call  save it BATCH script and will sent path of batch script to CONTORL-M Operataor:

       

      ---- My existing script ----

      DECLARE @name VARCHAR(50) -- database name

      DECLARE @path VARCHAR(256) -- path for backup files

      DECLARE @fileName VARCHAR(256) -- filename for backup

      DECLARE @fileDate VARCHAR(20) -- used for file name

       

      -- specify database backup directory

      SET @path = '\\.............'

       

      -- specify filename format

      SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

       

      DECLARE db_cursor CURSOR READ_ONLY FOR

      SELECT name

      FROM master.sys.databases

      WHERE name NOT IN ('tempdb','ReportServerDev','ReportServerQA','ReportServerTempDBDev','ReportServerTempDBQA')  -- exclude these databases

      AND state = 0 -- database is online

      AND is_in_standby = 0 -- database is not read only for log shipping

       

      OPEN db_cursor

      FETCH NEXT FROM db_cursor INTO @name

       

      WHILE @@FETCH_STATUS = 0

      BEGIN

         SET @fileName = @path + @name + '_' + @fileDate+ '_DIFF' + '.BAK'

         BACKUP DATABASE @name TO DISK = @fileName   WITH  DIFFERENTIAL

       

         FETCH NEXT FROM db_cursor INTO @name

      END

       

       

       

      CLOSE db_cursor

      DEALLOCATE db_cursor