Product: MS SQL 2000 Enterprise Edition
Goal: Off-site backup of database without onsite tech intervention
* Webhost does not want SQL Server Agent running for fear of viruses
* Database is 3.4GB, LDF is 3.7GB, and a Full Backup is 3.1GB.
Connection / Access: Enterprise Manager, RDP and FTP all work to access data and backups.
I have looked into doing a full backup then differential backups, doing transaction replication, and log shipping. The client doesn't really need replication, he just wants to know that if his server with the production database goes up in flames he has a way of getting back in business without losing more than one day of data.
I am hoping there is a method of "restoring" on his off-site server the data that is "backed up" on the production server. Is that possible if we're doing differential backups?
Is that the best solution or is there a better way?
Thanks for any recommendations or advice,
Yes, my web host turned off SQL Agent because he said "we should never have this running, except when you need to use it. The SQL worms (like slammer) use agent to do their dirty work."
I guess he doesn't understand that when I "need to use it" will be when I'm asleep. It kind of defeats the purpose of having it if I have to wake up every time I want to use it to trigger something...
Yes, currently I'm backing up manually every day or two and crossing my fingers a lot. It's a bad way to go so I'm looking for alternatives. I could probably persuade the webhost to allow SQL Agent to be on if I give him good enough reasons.
Tell them it's nonsense. As long as you have SP3 on the box, and your SA has some password (preferrably something not easily guessable), the presence of SQLAgent service will have no bearing on "sqlworm" or any of the stuff they are talking about. In fact, I don't think they know what they are talking about. You want me to call them? I really need to yell at someone, everybody here avoids me (I NEED MY CIG!!!)
Ok, I think I can convince them to allow SQL Agent with the right passwords in place. They are using Win 2000 but I'm not sure what SP version they are on currently.
From personal experience I would rather mow lawns for a living than install SP3 on any of my computers. Last time I did that it was worse than any virus I've ever had. I had to reformat the HD and reinstall everything from scratch - I'm now at SP2 and will happily stay here forever. If the webhost must upgrade I'll recommend jumping to SP4.
Back to the original question... what is the best solution for the client? Backup, replication, log shipping, or something else?
He needs to have something off-site in case the webhost server blows up and the data is lost on the production server. It is Ok if the data is a day old, but more than that and business could be ruined.
OK, first of all, who told you that SP3 for SQL is evil? Show me that guy... Second, if you had issues installing SP3 or SP3a on a SQL box, it means you had something inheritantly wrong with it, maybe even on the OS level, like some incompatible HW or something.
So is this a valid backup solution, just to confirm this is possible...
Full Backup on 1st and 15th of each month
All other days do Differential Backup
On 1st and 15th download via FTP Full Backup and do a full Restore.
Every other day of the month download via FTP the Differential Backup and do a Differential Restore so it will be in-sync with the production server.
That was one of my original plans, but I wasn't sure if you can do a Differential Restore on a server that did not create the Differential Backup. Is it possible? If so can you tell me where in the Help files to find the steps?