Post reply

Real Newbie Backup Question

  • February 6, 2006 at 12:52 pm

    #111468

    Hello,

    I have been tasked with writing a spec for a new web app we are going to be running. The app will use SQL Server as its backend data source. The SQL Server database will reside on its own server separate from the app. The requirements indicate that we should plan to maintain a backup copy of this database.

    My question is, do we need another separate server for the SQL Server backup or can the backup reside on the same server as the production db?

    Obviously, this is not my area of expertise.

    Thanks in advance,

    Mike

  • February 6, 2006 at 1:02 pm

    #619065

    Hello Mike,

    Yes and No.

    You can have the backups on the same server. Suppose, if the server crashes and the backup files are not recoverable then you will be at a loss.

    So, it would be better to have the backups routed to a shared network machine, where your network colleagues take the daily backups on to the tapes.

    Hope you are clear.

    Thanks and have a nice day!!!


    Lucky

  • February 6, 2006 at 1:08 pm

    #619066

    It depends on how dire the consequences of data loss and/or application unavailability are. If your users can go for a couple of weeks without the application, tape backups of the database backups (stored offsite, hopefully) might be adaquate - two weeks should allow for enough time to order hardware, install the OS and SQL, and restore the backups, etc.

    If the application or the data is critical to your operations, another server is probably needed, and you might as well automatically copy the backups over to it, restore them, and test them on a regular basis.

  • February 6, 2006 at 1:13 pm

    #619068

    That all makes sense. We would need to get the app back up and running ASAP, so I think another server would be the best solution.

    Could that be done directly with SQL Server, or would we need some type of backup software?

    -Mike

  • February 6, 2006 at 1:36 pm

    #619076

    It can be done directly with SQL Server, using the BACKUP and RESTORE commands. These work fine for all but the largest databases. Third-party backup utilities exist which can do compression, etc., but the built-in commands are generally considered the most reliable. The Enterprise Edition of SQL Server has a feature called Log Shipping, which automates copying and restoring logs to another server, but it is not that difficult (and more flexible) to program that functionality yourself.

  • February 6, 2006 at 2:23 pm

    #619092

    It is not necessary to backup to a different server if you can plug a second disk into your main server.  This way even if you lose the server you can swap the disk into another machine or rebuild the operating system without reformating the second disk.

    If you would rather backup to a shared network device you can do this with the native SQL backup commands by specifying a UNC path as your backup device.  Some things to watch out for:

    Make sure that the sql server service is running as a domain account or at least an account that has access to the network share. 

    Also, if the database will be of any substantial size, this backup will consume a lot of network recources (bandwidth).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply