December 3, 2007 at 2:16 am
Dear all
I have come to realise the hard way that SQL agent on MS SQL 2000 does not work properly. That is after not beng able to restore the backups i had and reading some articles that clarify the problems that sql agent has.
So i was wondering if anyone has any ideas to share about how I can perform backup of a SQL 2000 database. I have a requirement to do a full backup of the database at least once a day so in case of an emergency i can put the system back to at least a day old data.
I was looking at mirroring but that is not possible for SQL 2000. Another option that i was lookign at was to replicate the SQL 2000 database to a SQL 2005 database and then use the sql agent to backup that database where the sql agent aparently has the bugs resolved.
Would anyone be able to provide any advice from ecperience of how they have tackled this problems? Any white papers? Examples?Books?Articles?
Thank you so much for your help and time.
Cheers
December 3, 2007 at 7:34 am
Dan Gong (12/3/2007)
I have come to realise the hard way that SQL agent on MS SQL 2000 does not work properly.
What do you mean by that ?
I've been working for almost 10 years with SQL Server and I can't rmember any issues with SQL Agent which would lead to unrestorable backups. In fact I and a lot of other DBA's prefer the native SQL Server backup over most third-party SQL Agents because it's much more reliable.
[font="Verdana"]Markus Bohse[/font]
December 3, 2007 at 10:52 am
I'm using sql agent too as backupper and restorer.
Prod server: sql agent daily backup database to networklocation...
Standby server: sql agent daily restore backup. If any error comes up the backup had failed and needs to be redone as soon as possible.
Backup straight on the network poses a bit more problems than on a local disk because the sql server backup is quite sensible on network issues.
December 4, 2007 at 4:48 am
Dan,
How are you backing up? T-SQL or with a maintenance plan? Or are you using a third party tool?
Where are you backing up? Tape Drive, disk drive, UNC path or some other backup device (if other, please describe).
What errors do you get when you try to restore?
Do the logs show any errors when the backup job is running?
December 7, 2007 at 9:16 am
Please give us a clue here.
Worst case, set up a Windows Scheduled Task if you can't get SQLAgent to work.
Run isql -E -Q"backup database xxx to disk='c:\mybackup.bak' with init"
Feel free to change paths and db name 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply