SQL BACKUP FAILED (?) Job file missing

  • JnrDbaK

    Say Hey Kid

    Points: 676

    First of all apologies if I'm not posting this in the right section, just started my career as Jnr Dba in a new company & I will have several questions to ask. For example

    We currently have monthly backups running at the 1st of each month for our production DBs, these are scheduled by Litespeed console (like any other backup schedule we've got set up) and included in a maintenance plan.

    Now the monthly backup for one of the servers which I believed it run gives me an error on the sql job monitor, however when I check the location in where the backup files are held, everything is there, each file with the right size; for some reason I do not have a log report on the server itself, seems to be blank however on the log file summary I get this:

    Message

    Executed as user: xxx\SQL_AGENT_USER. ...B34' with Windows Authentication Logged on to SQL Server 'xxx' Starting maintenance plan '##DBA - Backup' on 01/11/2016 03:05:13 [1] Enum database list... Selected database list: abc def klm nop ghs qwe [1] Execution Time: 0 hrs, 0 mins, 1 secs. [2] Database "abc": Backup database... SQL> Processed 12990680 pages for database 'abc', file 'abc' on file 1. SQL> Processed 39 pages for database 'abc', file 'abc' on file 1. SQL> BACKUP DATABASE successfully processed 12990719 pages in 1513.398 seconds (70.318 MB/sec). Backup added as file number: 1 Output File(s): \\1.2.3.4\DB_Backups\Monthly\x\xxx\abc_db_xxx_2016-11-01-030513_Tue_.bak Native Size: 101... The step failed.

    Server type sql server 2000 8.00.2305

    Questions:

    did this job fail even though on the server I can see the backup files?

    did it fail for only one DB, "abc"?

    Where else can I find a more detailed report if the job log file on the server is blank?

    I hope I've been clear enough, apologies again if this is not the right section.

    Thanks for all your help

    To know where you're heading, you gotta know where you been :alien:

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    To me it looks like a Maintenance Plan related error, strongly suggest to switch to Ola Hallengren's SQL Server Maintenance Solution[/url], much more robust.

    😎

    Question, what if any errors are there in msdb.dbo.sysjobhistory, Windows Event Log or the SQL Server Error Logs?

  • Ed Wagner

    SSC Guru

    Points: 286942

    Eirikur Eiriksson (11/12/2016)


    To me it looks like a Maintenance Plan related error, strongly suggest to switch to Ola Hallengren's SQL Server Maintenance Solution[/url], much more robust.

    😎

    Question, what if any errors are there in msdb.dbo.sysjobhistory, Windows Event Log or the SQL Server Error Logs?

    Eirikur, does Ola's backup script handle SQL 2000?

    JrDbaK, welcome to SSC and congratulations on your Jr. DBA position. I'd strongly suggest taking Eirikur's advice on Maintenance Plans - do away with them. Also, check out the Books link along the left side of this site. There are a lot of really good books available to download in PDF to help you learn.

    First things first - figure out your backups and make sure they support the business requirements for recovery. Make sure they're viable by doing "practice" restores so the first time you do a restore isn't in an actual emergency with the boss breathing down your neck.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Ed Wagner (11/12/2016)


    Eirikur Eiriksson (11/12/2016)


    To me it looks like a Maintenance Plan related error, strongly suggest to switch to Ola Hallengren's SQL Server Maintenance Solution[/url], much more robust.

    😎

    Question, what if any errors are there in msdb.dbo.sysjobhistory, Windows Event Log or the SQL Server Error Logs?

    Eirikur, does Ola's backup script handle SQL 2000?

    JrDbaK, welcome to SSC and congratulations on your Jr. DBA position. I'd strongly suggest taking Eirikur's advice on Maintenance Plans - do away with them. Also, check out the Books link along the left side of this site. There are a lot of really good books available to download in PDF to help you learn.

    First things first - figure out your backups and make sure they support the business requirements for recovery. Make sure they're viable by doing "practice" restores so the first time you do a restore isn't in an actual emergency with the boss breathing down your neck.

    No Ed, Ola Hallengren's SQL Server Maintenance Solution are supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016.

    😎

  • Ed Wagner

    SSC Guru

    Points: 286942

    Eirikur Eiriksson (11/13/2016)


    Ed Wagner (11/12/2016)


    Eirikur Eiriksson (11/12/2016)


    To me it looks like a Maintenance Plan related error, strongly suggest to switch to Ola Hallengren's SQL Server Maintenance Solution[/url], much more robust.

    😎

    Question, what if any errors are there in msdb.dbo.sysjobhistory, Windows Event Log or the SQL Server Error Logs?

    Eirikur, does Ola's backup script handle SQL 2000?

    JrDbaK, welcome to SSC and congratulations on your Jr. DBA position. I'd strongly suggest taking Eirikur's advice on Maintenance Plans - do away with them. Also, check out the Books link along the left side of this site. There are a lot of really good books available to download in PDF to help you learn.

    First things first - figure out your backups and make sure they support the business requirements for recovery. Make sure they're viable by doing "practice" restores so the first time you do a restore isn't in an actual emergency with the boss breathing down your neck.

    No Ed, Ola Hallengren's SQL Server Maintenance Solution are supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016.

    😎

    I didn't think so, but wasn't sure and didn't spend the time to download and review it.

  • JnrDbaK

    Say Hey Kid

    Points: 676

    Eirikur Eiriksson (11/12/2016)


    To me it looks like a Maintenance Plan related error, strongly suggest to switch to Ola Hallengren's SQL Server Maintenance Solution[/url], much more robust.

    😎

    Question, what if any errors are there in msdb.dbo.sysjobhistory, Windows Event Log or the SQL Server Error Logs?

    Thank you I will have a look, well the msdb.dbo.sysjobhistory was showing me the same error and the event log didn't show any failure.

    To know where you're heading, you gotta know where you been :alien:

  • JnrDbaK

    Say Hey Kid

    Points: 676

    Ed Wagner (11/12/2016)


    Eirikur Eiriksson (11/12/2016)


    To me it looks like a Maintenance Plan related error, strongly suggest to switch to Ola Hallengren's SQL Server Maintenance Solution[/url], much more robust.

    😎

    Question, what if any errors are there in msdb.dbo.sysjobhistory, Windows Event Log or the SQL Server Error Logs?

    Eirikur, does Ola's backup script handle SQL 2000?

    JrDbaK, welcome to SSC and congratulations on your Jr. DBA position. I'd strongly suggest taking Eirikur's advice on Maintenance Plans - do away with them. Also, check out the Books link along the left side of this site. There are a lot of really good books available to download in PDF to help you learn.

    First things first - figure out your backups and make sure they support the business requirements for recovery. Make sure they're viable by doing "practice" restores so the first time you do a restore isn't in an actual emergency with the boss breathing down your neck.

    Thank you Ed I will have definitely have a look, I been looking for scripts too at the same time, don't always have time to do practice restores since of the workload that I'm but cheers for the advice

    To know where you're heading, you gotta know where you been :alien:

Viewing 7 posts - 1 through 7 (of 7 total)

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