Backup plan goes wrong
-
Hello all...
We have a SQL Server 2000 running for the last month on a dedicated server with no problems, in it there are about 7 different database, when we first created the DB I created a backup plan to backup all 7 DB's including transaction logs but without checking integrety or doing optimazation... and creating a specific folder for each DB with deleting any files older than 4 weeks.
This went well and was backing up fine for the last month. I am not aware of any changes I made in the server, however suddenly it stopped backing up and the Backup job comes up with a "failed" status.
I've looked into the Job history and got this msg:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionCheckForData (CheckforData()). [SQLSTATE 01000] (Message 4) General network error. Check your network documentation. [SQLSTATE 08S01] (Error 11). The step failed.
I looked into the logs on Event Viewer - Application and found this:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 19/05/2006
Time: 09:20:05
User: N/A
Computer: *****
Description:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0xEC30F1A28A6F344483EB3006C0640346) - Status: Failed - Invoked on: 2006-05-19 09:19:58 - Message: The job failed. The Job was invoked by User *****. The last step to run was step 1 (Step 1).
Please Help me... I'm completly lost
Cheers
Dagan
-
What was step 1? What was step 2?
There could be lots of reasons for this to happen, we just don't have enough information from you.
In Enterprise Manager, right click on the job and select job history. Check the box for show job steps. Then find the failed step....does it show any more information?
Some things to check.....does the step backup to a network drive? If so, was the drive available? Was the network connection good?
Have you changed the login/passord for the SQL Server Job Agent?
You say you are backing up all 7 databases, including doing TLog backups. If so, this job should have been failing all the time. The Master database is in SIMPLE mode and you can't backup the log. Check to see if that's the issue.
-SQLBill
-
ok...
got rid of the transact log backups and left only one DB to back up.
the job has one step in it called step1 when it fails it comes up with the job history notes as stated above "NT Authority bla bla bla"
Step 1 is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID E95E547C-E8ED-417B-9455-23E66F804287 -VrfyBackup -BkUpMedia DISK -BkUpDB "D:\SQLDB\backups" -DelBkUps 4WEEKS -CrBkSubDir -BkExt "BAK"'
the SA user has never been changed and as you can see the backup plan is executed to the Disk (not network) d: drive - which is live and running...
Sorry -
-
Did you right click on the job and select view job history? Then did you click on show job steps and find the step that failed?
That job has several steps in it (which is why I don't like maintenance plans - too tough to find out what went wrong). It could be the verify that failed, it could be the delete backup that failed.....
-SQLBill
-
Yes I did,
I also got rid of all the extra stuff, I've asked it not to Varify or backup anything else also not to optimize or minimize.. etc
So there is only one step and that is the backup - I went into the job history and got the error msg of the actual step - which is stated above in one of my posts: NT Authority something...
I also went into the job itself and into the step (the only step that i have - which is the backup) and pasted the code that is there to execute the plan...
SQLBill, if you do not use the maintenance plan what do you use to make sure you get a daily backup of your DB?
At the moment I'm reverting to a manual backup - but I'm obviously wanting this to be automated as what happens when I'm away... etc?
-
I create manual backup plans and schedule them as jobs.
For example, I have one job called Full Backups. Each step in that job does a full backup of one of my databases. Then I have a job called UserDiff, each step does a differential backup of my user databases. And the same with Transaction Log backups...one job for those.
-SQLBill
-
Cool
I just don't know how to create a backup step in a job...
Can you show me the code you use for the backup?
I guess you're executing some stored procedure???
How does this works?
-
Sometimes the Job History doesn't give the full story. In addition, I like to write out a log by going into each job step. then the "advanced" tab and setting up a text file to be written.
-
You really need to read the BOL on doing backups. But......
Let's set up a job to backup the master database to disk......
first you create a 'dump device':
USE Master
EXEC sp_addumpdevice 'disk', 'masterfull', 'E:\backup\masterfull.bak'
GO
--disk - tells it the backup will be to disk. masterfull is the name of the dump device, this is what you refer to when doing backups or restores. E:...etc is where on the disk the backup will be saved.
then you create the job.....
Use Enterprise Manager, go to jobs and select new job.
In the job step the code is:
BACKUP DATABASE Master TO Masterfull WITH INIT
See how the dump device name is used?
-SQLBill
-
Thanks guys - the manual backup works well for me...
instead of adding a 'dump device' I decided to go with this:
declare @backup as varchar(300)
set @backup = 'backup' + convert(varchar(100),day(getdate())) + '.' + convert(varchar(100),month(getdate())) + '.' + convert(varchar(100),year(getdate())) + '.bak'
BACKUP DATABASE northwind TO DISK = @backup
this way I can get a incremantation of the backup files i.e :
backup15.05.06.bak
backup16.05.06.bak
etc...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply