Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Location
    Panama City, Panama
    Posts
    5

    Unanswered: What is the best backup solution?

    SQL Gurus,

    What is the best backup solution?

    Product: MS SQL 2000 Enterprise Edition
    Environment: webserver

    Goal: Off-site backup of database without onsite tech intervention

    Obstacles:
    * 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,
    Alec Sherman

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SQLAgent is not running? So you're doing it manually every day/hour?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    uhh...get a new host?

    Hey anyone know what the backend for this site is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2004
    Location
    Panama City, Panama
    Posts
    5
    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.

    -Alec

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Contact the host here and find out what they do..now way this site isn't be backed up...

    NOOOOOOO all my posts...GONE!

    Now I have to remeber...what a sin.....

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!!!)

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You quitting?

    I was contemplating it....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nah, chewing Nicorette, waiting for the clock...IT'S NOT MOVING!!!

  9. #9
    Join Date
    Mar 2004
    Location
    Panama City, Panama
    Posts
    5

    SP3, isn't that evil?

    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.

    Any recommendations?

    -Alec

  10. #10
    Join Date
    Mar 2004
    Location
    Panama City, Panama
    Posts
    5

    SP4 evil - not SP3

    Pardon me, bad memory. It was Windows 2000 SP4 that ruined a weekend, not SP3. If anyone's interested this is why I switched back from SP4 to SP3.

    http://download.com.com/3302-2098_4-...html?pn=2&fb=2

    -Alec

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'd stick with automated backup. Everything else will require knowledge which your client probably doesn't have.

  13. #13
    Join Date
    Mar 2004
    Location
    Panama City, Panama
    Posts
    5
    So is this a valid backup solution, just to confirm this is possible...

    Production Server
    Full Backup on 1st and 15th of each month
    All other days do Differential Backup

    Client Server
    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?

    Thanks for your help!
    -Alec

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If your client has a couple of grand to spare, - get SQLLiteSpeed, and you won't have to mess with diff backups.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •