• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

SQL Server 2000 Replication to same server?

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?  
0
eangle
Asked:
eangle
  • 3
2 Solutions
 
twoboatsCommented:
Take a backup and restore it to the seconmd database. As you only want it daily, this will be fine
0
 
twoboatsCommented:
You can set up sql jobs to automate this
0
 
lahousdenCommented:
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.
0
 
twoboatsCommented:
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.


0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now