Backup Job Incomplete

  • Hi,

    I have problem My backup script runs on every sunday for full backup and should have to take 41 database backup but it only takes 22 or 7 databases backup and and it say that it ran successfully. Can please help me

  • can share script? so we can check more details and also check error log.There should all details.

  • The login that is running the backup script has enough permission for missing databases?

    Any of the database are in restoring mode?

    Please post your script to analyse.

  • Are you using sp_MSforeachdb by any chance? Please post the script you're using.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • DECLARE @dbname VARCHAR(max),

    @fullfilename VARCHAR(max),

    @getfullfileq VARCHAR(max),

    @fullfilebackupq VARCHAR(max),

    @deldate datetime,

    @getdate-2 VARCHAR(30),

    @gettime VARCHAR(8),

    @hh VARCHAR (2),

    @mm VARCHAR(2),

    @ss VARCHAR(2)

    /*This command will get all Producation databases with any recovery model*/

    DECLARE getdbname CURSOR FOR

    SELECT nameFROM sys.databases

    Where name in ('test1','test2','test3','test4','test5','test6',test7')

    OPEN getdbname

    FETCH NEXT FROM getdbname

    INTO @dbname

    WHILE @@FETCH_STATUS =0

    BEGIN

    /* Below section will convert current date and time into varchar so we can put as file name*/

    SET @getdate-2= CONVERT(VARCHAR(30), GETDATE(),110)

    SET @gettime=CONVERT(VARCHAR(8), GETDATE(),108)

    SET @hh = SUBSTRING(@gettime,1,2)

    SET @mm = SUBSTRING(@gettime,4,5)

    SET @ss = SUBSTRING(@gettime,7,8)

    /*Below command will store backup full file query

    Make path modification so we can use from any database*/

    SET @fullfilebackupq ='BACKUP DATABASE ['+@dbname+']

    TO DISK=''E:\Full\'+@dbname+'_full-BACKUP_'+@getdate+'-'+@hh+'-'+@mm+'-'+@ss+'.bak''

    WITH

    RETAINDAYS = 30,

    NOFORMAT,

    NOINIT,

    NAME ='''+@dbname+'fullbackup'+@getdate+'_'+@gettime+''''

    /*Here query executed*/

    --print @fullfilebackupq

    EXEC( @fullfilebackupq)

    FETCH NEXT FROM getdbname

    INTO @dbname

    END

    CLOSE getdbname

    DEALLOCATE getdbname

  • I have sysadmin write and I all database is online.

    These are prductiong databases

  • This:

    /*This command will get all Producation databases with any recovery model*/

    DECLARE getdbname CURSOR FOR

    SELECT nameFROM sys.databases

    Where name in ('test1','test2','test3','test4','test5','test6',test7')

    will get you 7 databases: test1','test2','test3','test4','test5','test6',test7'.

    If this is a "Sanitized" script it does us no good.

  • I means there is name upto 33 databases test1 to test33

  • Are you capturing the output of the job step to a log file or table, the options in job step advanced tab?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Log file and log file result is below

    Executed as user: NT AUTHORITY\NETWORK SERVICE. ...

    'test1' on file 1.

    [SQLSTATE 01000] (Message 4035) Processed 2 pages for database 'test1', file 'test_log' on file 1.

    [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 136818 pages in 19.594 seconds (54.551 MB/sec).

    [SQLSTATE 01000] (Message 3014) Processed 3150240 pages for database 'test2', file 'test2' on file 1.

    [SQLSTATE 01000] (Message 4035) ... The step succeeded.

  • Mind sharing what was logged for the run where you missed backing up some databases?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • jignesh.swami82 (5/22/2013)


    I means there is name upto 33 databases test1 to test33

    Okay, if you list 33 database names how do you expect the procedure to backup 41 databases as you indicate in your original post?

    Original post:

    jignesh.swami82 (5/21/2013)


    Hi,

    I have problem My backup script runs on every sunday for full backup and should have to take 41 database backup but it only takes 22 or 7 databases backup and and it say that it ran successfully. Can please help me

  • can check database log is full? Try for once database which not backup,Try take manual backup and check is there any problem ?Space available on disk ?

  • May be there are trailing spaces in the end of database name. Worth checking !! "DBA" is not same as "DBA " ..

  • Thank you all reply

    I did two Thing

    1) I ran my script manaully it backup and same problem so I took backup inducel database and it work fine by using same script

    2)check space I have 1.7 TB free space

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply