SQL Server Performance

"Warm" Spare DB - how to script?

Discussion in 'General Developer Questions' started by rweinstein, Nov 12, 2004.

  1. rweinstein New Member

    I am looking into the possibility of keeping a "warm" spare of my Prod DB on my backup server. I would like to synchronize them only twice a day and don't think I need to implement a full complex log shipping solution.

    It would suit my needs to just do the following steps twice a day:

    Backup my prod DB
    Copy the prod DB to my backup server
    Restore the prod DB on my backup server.

    What process would I go about automating or scripting this the best, or easiest way?

    Can I use Batch scripting? Should I use VB? Can I use scheduled utilities or procedures in SQL Server? Should I use a third party tool such as LiteSpeed?

    Any input/code is appreciated.
  2. Jeffrey_D_York New Member

    I'm still a new guy when it comes to SQL, but I have used log shipping and it really isn't TOO complex to set up. You can set the logs to copy over at any interval. How transaction heavy is the DB? your logs may get big if it is very transaction heavy.
    http://www.sql-server-performance.com/sql_server_log_shipping.asp


    How big is the DB?
    once you get up there in size, I think any scripted solution or DTS would be resource intesive unless of course you do a differential or incremental.

    However, if you really don't want to log ship, couldn't you use a DTS package with a SQL task that does a backup
    to the location you want and another SQL task to restore it to the warm spare? You can schedule the DTS package. Or you can just set up SQL Agent jobs to accomplish as well.


    Another question I have is why restore it at all to a back up server? If your DB is small you can restore it on demand to the back up server. I would like to know more about your backup/disaster recovery mehtodology to be able to help more. How 'warm' is this stand by server- what needs to change in order to be able to make it a production server, etc. Let me know, I and I am sure others have good opinions on a best practice for you. Thanks

    BOL has all the syntax you need for scripting a backup as well.
  3. rweinstein New Member

    DB is about 18 GB.

    I do like the DTS to backup and DTS to restore option, but when a new backup is created, the filename has the date and time in the name. How wil the DTS restore be set to restore a dynamic filename? Can this be done? I'll check BOL again.

    Our downtime window right now is about an hour or two with the restore on a backup server. Our users pretty much accept this, so its not that critical to up the level, but I was thinking about automating the restore twice a day, so the DB will not be more than 12 hours out of synch. I would like to test it, but it is not critical right now to implement, just a great idea to up our level of high-availability.

    Thanks.

  4. Jeffrey_D_York New Member

    Can you just use a device like Backup.bak and use the INIT to always overwrite the media

    From bol..
    "..The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media."

    The reason I ask about down time is that it could potentially take a lot to point the users and applications to a new DB server. You need to have all the logins and DB Users on the back up server. Still there is potential for headaches as the SID for the user(From the DB you restored from) more than likely will not match teh sid of a login. There are scripts to make them sync, but be aware that you would have to run them every time.

    ODBC connections would have to change too especially if the server that is in your production network (NetBios naming, IP address, etc). You would have to be able to point to the new server.

    Let's keep at this and we can hammer out some more detail. Thanks!
  5. satya Moderator

Share This Page