December 7, 2004 at 3:59 pm
Got a question for you DBA's. I'm not a DBA, don't even aspire to be one, However, we don't have one so I have to do some of the functions of one in the blind of course
My question is this. I have backup jobs in Sql Server agent that Start around 2:15 am and the status is successful after about 1.5 hrs. However the physical file on the disk is displaying a modified time of 16:00. That's a 12 hour difference. Any ideas? We seem to be getting several backup failures and we cannot determine why. Of course, it would probably help if we had a DBA that knew what they were doing, but we don't so......
Any input on this would be helpful.
December 8, 2004 at 12:54 am
Hi,
what kind of backup is that: complete, transactional, differential maybe ? Are there any other backup jobs, targeting the same backup file ? What is the backup error message ? You can simulate the job execution by copying the job step text in SQL Query Analyzer, under same security account and database context as the job executes. If some error occurs, the message will be much helpfull. Also, if it MS SQL 2000, please check the database recovery model (in the options page of the database properties dialog).
Regards,
Goce.
December 8, 2004 at 8:47 am
Try checking the SQL error log file. It should give you information of what database is backed up when and also what kind of backup it is.
December 8, 2004 at 9:10 am
It is sql 2000 on windows 2003. The backups that are failing are of various types full and simple. They are complete backups. The error log just says that it failed. Interestly I found one database that Sql Agent reported as successful but had a failed entry in the sql log. I don't have a clue what's going on but I have recommended to my boss that they pay someone who knows what they are doing to look at it. I would hate to find out that our backups are unreliable when we need them the most!
December 8, 2004 at 9:31 am
Definitely don't want that. Good advice to your boss, though I'd check a few backups and restore them on another server or this one as a test.
Are you writing to new files everyday? Or the same one? how did you setup the backup jobs?
December 9, 2004 at 6:55 am
Some are new files, others write over the same. I changed the time of one of the backups and that seems to have fixed it. The one that had a failed entry in the log but said successful in Sql Agent is writing to the same file daily. It is a 55 gig backup(Database is simple recovery mode). Unforturnately, our network admin was complaining about the tape backups and thus did not want multiple 55 gig files out there. Normally we try to keep at least 2 days worth of backups on the drive at a time otherwise we have to go back to tape to get an older backup. The jobs were set up by various methods. Some as part of Maintenance Plans, others just a backup jobs. Multiple people are setting up these jobs and that is part of the problem, no consistency and no one who really knows what they are doing. Real recipe for disaster!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply