Database backup job failure

Hi,
I'm new to sql server. Had been having issues with backups for last 3 weeks.
On 1 server, my main database "DB1" recovery model option is set to Simple.
The backups run every night. I have set the maintenance plan to delete the
file older than 12 hours. All the steps complete successfully except 1 step.
The error shows "can not delete DB1 backup file".
Another question I have is, if a db is set to 'Simple' recovery mode, the
transaction log fails right?
Please help!!!!!!
Thank you> Another question I have is, if a db is set to 'Simple' recovery mode, the
> transaction log fails right?
Yes, it will fail. You can not backup the transaction log aof a db that has
simple recovery model.
Check if the account being used by sql agent service has rights to delete
from that folder.
AMB
"helpplease" wrote:
> Hi,
> I'm new to sql server. Had been having issues with backups for last 3 weeks.
> On 1 server, my main database "DB1" recovery model option is set to Simple.
> The backups run every night. I have set the maintenance plan to delete the
> file older than 12 hours. All the steps complete successfully except 1 step.
> The error shows "can not delete DB1 backup file".
> Another question I have is, if a db is set to 'Simple' recovery mode, the
> transaction log fails right?
> Please help!!!!!!
> Thank you|||Need more info here. What do you mean by "set the maintenance plan to
delete the file older than 12 hours" - which file? The backup file?
Why are you deleting the backup file? Don't you want the backup file
to hang around? If you include the INIT parameter in your backup
statement, it just reinitializes the backup file and you can overwrite
the previous days. You can just leave the file there and the job will
backup over the file that's already there....include your script and
more error info....right-click job and "view job history" then "show
step details".
Example -
BACKUP DATABASE master
TO DISK = 'c:\sqlbackups\Full\master_dump.BAK'
WITH INIT ,
NAME = 'master',
DESCRIPTION = 'master INIT BackupFull'
helpplease wrote:
> Hi,
> I'm new to sql server. Had been having issues with backups for last 3 weeks.
> On 1 server, my main database "DB1" recovery model option is set to Simple.
> The backups run every night. I have set the maintenance plan to delete the
> file older than 12 hours. All the steps complete successfully except 1 step.
> The error shows "can not delete DB1 backup file".
> Another question I have is, if a db is set to 'Simple' recovery mode, the
> transaction log fails right?
> Please help!!!!!!
> Thank you|||How do I check this.
"Alejandro Mesa" wrote:
> > Another question I have is, if a db is set to 'Simple' recovery mode, the
> > transaction log fails right?
> Yes, it will fail. You can not backup the transaction log aof a db that has
> simple recovery model.
> Check if the account being used by sql agent service has rights to delete
> from that folder.
>
> AMB
> "helpplease" wrote:
> > Hi,
> >
> > I'm new to sql server. Had been having issues with backups for last 3 weeks.
> > On 1 server, my main database "DB1" recovery model option is set to Simple.
> > The backups run every night. I have set the maintenance plan to delete the
> > file older than 12 hours. All the steps complete successfully except 1 step.
> > The error shows "can not delete DB1 backup file".
> >
> > Another question I have is, if a db is set to 'Simple' recovery mode, the
> > transaction log fails right?
> >
> > Please help!!!!!!
> >
> > Thank you|||Browse to the folder in windows - right click and view the
security...see if the sql service acct. has permissions on the
folder. If you don't know who the sql service acct is - go to control
panel, administrative tools, services, and double click on mssqlserver.|||In the Database Maintenance Plan, under 'Complete Backup' tab there is an
option that says 'Remove files older than'. I have this option checked, with
the value 12 hours. Backup file extension is BAK.
Under view job history all I see is this
"Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed."
Under Maintenance Plan History all I see under status failed is this
Activity is "Delete old DB backup files" Status is failed.
"unc27932@.yahoo.com" wrote:
> Need more info here. What do you mean by "set the maintenance plan to
> delete the file older than 12 hours" - which file? The backup file?
> Why are you deleting the backup file? Don't you want the backup file
> to hang around? If you include the INIT parameter in your backup
> statement, it just reinitializes the backup file and you can overwrite
> the previous days. You can just leave the file there and the job will
> backup over the file that's already there....include your script and
> more error info....right-click job and "view job history" then "show
> step details".
> Example -
> BACKUP DATABASE master
> TO DISK = 'c:\sqlbackups\Full\master_dump.BAK'
> WITH INIT ,
> NAME = 'master',
> DESCRIPTION = 'master INIT BackupFull'
> helpplease wrote:
> > Hi,
> >
> > I'm new to sql server. Had been having issues with backups for last 3 weeks.
> > On 1 server, my main database "DB1" recovery model option is set to Simple.
> > The backups run every night. I have set the maintenance plan to delete the
> > file older than 12 hours. All the steps complete successfully except 1 step.
> > The error shows "can not delete DB1 backup file".
> >
> > Another question I have is, if a db is set to 'Simple' recovery mode, the
> > transaction log fails right?
> >
> > Please help!!!!!!
> >
> > Thank you
>|||My newsreader screwed up so sorry if this ends up being a repost ...
If I'm understanding you, you have a backup running every 24 hours, but
you want to delete the file after 12 hours' This leaves you a 12 hour
window without a backup. Not advisable.
I'm not a big fan of the wizards - just an opinion. If you are simply
shooting for a daily backup of your databases...don't do the database
maintenance wizard. Create a new job - Enterprise manager, browse to
your server, management, sql server agent, jobs. Create a new job, &
job sql step. Issue the backup command. Then schedule your job
(schedule tab).
BACKUP DATABASE master
TO DISK = 'c:\sqlbackups\master_dump.BAK'
WITH INIT ,
NAME = 'master',
DESCRIPTION = 'master INIT BackupFull'|||If I'm understanding you correctly, you're taking full backups every
day with simple recovery (every 24 hours), but deleting them after 12
hours - this leaves a 12 hour window where you have no backups. So I'm
still unclear as to why you'd want to do this'
It sounds like you just want to create a backup schedule, but not
retain backups forever. My vote would be to skip the database
maintenance wizard altogether and create a new job (in enterprise
manager, browse to server, then management, then sql server agent, then
jobs) . Create a new job, add a sql job step and execute the backup
command, replacing the parameters below with parms specific to your
situation. Use the INIT parm to reinitialize the media header on the
file....You can then schedule your job (schedule tab) to run every 24
hours or whatever you want. See Books online BACKUP command for all
the other parameters.
BACKUP DATABASE master
TO DISK = 'c:\sqlbackups\master_dump.BAK'
WITH INIT ,
NAME = 'master',
DESCRIPTION = 'master INIT BackupFull'
helpplease wrote:
> In the Database Maintenance Plan, under 'Complete Backup' tab there is an
> option that says 'Remove files older than'. I have this option checked, with
> the value 12 hours. Backup file extension is BAK.
> Under view job history all I see is this
> "Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed."
> Under Maintenance Plan History all I see under status failed is this
> Activity is "Delete old DB backup files" Status is failed.
>
> "unc27932@.yahoo.com" wrote:
> > Need more info here. What do you mean by "set the maintenance plan to
> > delete the file older than 12 hours" - which file? The backup file?
> > Why are you deleting the backup file? Don't you want the backup file
> > to hang around? If you include the INIT parameter in your backup
> > statement, it just reinitializes the backup file and you can overwrite
> > the previous days. You can just leave the file there and the job will
> > backup over the file that's already there....include your script and
> > more error info....right-click job and "view job history" then "show
> > step details".
> >
> > Example -
> >
> > BACKUP DATABASE master
> > TO DISK = 'c:\sqlbackups\Full\master_dump.BAK'
> > WITH INIT ,
> > NAME = 'master',
> > DESCRIPTION = 'master INIT BackupFull'
> >
> > helpplease wrote:
> > > Hi,
> > >
> > > I'm new to sql server. Had been having issues with backups for last 3 weeks.
> > > On 1 server, my main database "DB1" recovery model option is set to Simple.
> > > The backups run every night. I have set the maintenance plan to delete the
> > > file older than 12 hours. All the steps complete successfully except 1 step.
> > > The error shows "can not delete DB1 backup file".
> > >
> > > Another question I have is, if a db is set to 'Simple' recovery mode, the
> > > transaction log fails right?
> > >
> > > Please help!!!!!!
> > >
> > > Thank you
> >
> >