MSQL - How can I keep a backup database up to date.

Posted on 2011-10-24
Last Modified: 2012-05-12
Situation.  I have two servers.  A production and a backup.  I want to make sure the production server is backing its MSQL database to the backup.  Is there a way to update the backup database rather then a full backup?  The idea is all I would have to do is change the IP of the backup server.
Question by:Bgauth
    LVL 3

    Expert Comment

    Maybe this is something you could find useful?
    LVL 9

    Expert Comment

    It's need to be down time to do this task.
    or you can use the reflection or database mirroring.
    LVL 4

    Accepted Solution

    Hi Bgauth,

    If I understand you correctly, you have 'DatabaseA' on 'ServerA' and 'Database B' on 'ServerB'.  You want 'DatabaseB' on 'ServerB' to always be the same as 'DatabaseA' on 'ServerA'?

    I believe this can be done with Database mirroring in SQL, however if you want a simple, yet effective way to get much the same result, you could do the following:

    1. Setup an SQL Database backup on Server A to backup Database A once a day or twice a day, depends how up to date you want your backup to be in the case you need to restore it.
    2. Set the location of the backup to a share on the 'backup' server - eg \\ServerB\sqlbackup
    3. In the case the primary goes down, you will always have the most recent backup stored on your backup server.
    4. When the primary goes down, simply open SQL manager and restore the database backup file on ServerB.
    5. redirect your SQL frontend applications to point to 'sql:\\ServerB\databasename' rather than sql:\\ServerA\databasename'

    This is more of a small business solution, whereby implementing proper SQL mirroring would be over complicating things.  Below are some articles on proper SQL mirroring if you like to read those and compare the 2 methods.

    The third link provides a good overview and guide, in a quick manner.

    Let me know what you decide to do and I'll anymore insight I can.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now