SQL Server 2000 Replication to same server?

Posted on 2007-07-26
Last Modified: 2012-06-21
Have a SQL Server 2000 Standard SP4 server hosting a production accounting database.  Want to make a daily copy to a non production/test database on same server so accountants can work with current data without affecting production.  Want to automate this so I don't have to make a manual copy every day of course.  

Will Replication work for this or will this only work for replicating to a remote/separate server?  Is there a better way to accomplish this task using a different method altogether?  
Question by:eangle
    LVL 14

    Expert Comment

    Take a backup and restore it to the seconmd database. As you only want it daily, this will be fine
    LVL 14

    Expert Comment

    You can set up sql jobs to automate this
    LVL 10

    Accepted Solution

    Publication-based Replication will work but there will be an ongoing overhead.  Log-Shipping Replication will basically accomplish what twoboats has proposed (so it doesn't have the additional performance drain of publication-based Replication), and it can be part of your regular backup maintenace plan - the log-shipping wizard makes sure it is all set up to be fully automated.  To be able to query off the replicated database, you need to make sure you select the "Standby Mode" radio button in the Database Load State panel of the Add Destination Database window - this will ensure that the database is available in read-only mode.
    One slight "gotcha" with Log-Shipping Replication is that because the destination database is Read-Only you can't configure it for access directly - so you need to make sure that the logins you will be using to read the data all have the necessary read access on the original database - then that access will be log-shipped across to the Replica.
    LVL 14

    Assisted Solution

    Log shipping, will work, but imho is over engineering the solution if you just want a daily copy.

    Bear in mind - the log shipping can't be implemented along side any current log backups you do - it takes over.

    "Do not use the monitor server as the source server, because the monitor server maintains critical information regarding the log shipping system. The monitor server should be regularly backed up. Keeping the monitor server independent is also better for performance, because monitoring adds unnecessary overhead. Also, as a source server supporting a production workload, it is most likely to fail, which would disrupt the monitoring."

    So you'd want a second sql box to do the monitoring.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    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

    10 Experts available now in Live!

    Get 1:1 Help Now