Using the snapshot feature in SQL Server 2005 to replicate an operational database

Tags:
Replication
SQL Server 2005
SQL Server database
SQL Server replication
We are trying to keep a replicated copy of a customer’s operational database at our hosting facility. Our copy of their operational system will be used to run ETL against each night to refresh a data reporting database. Our hope is that, each night, we will receive a snapshot file and apply that to our copy. The customer has their operational system in-house, and they are looking to host their business intelligence platform or reporting database with us at our facility. Would the snapshot feature of SQL Server 2005 be a possibility to solve this problem, or is there another option within SQL Server 2005 that would be more suitable?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

The snapshot feature won’t help you out here.

You will want to get a full backup from them. Then each day have them self you a differential backup. Restore that differential backup over the full. Each time they take a new full you will want to have them send that to you.

After you do each restore make sure to leave the database is a readable state but where you can still apply a new backup to it.

You can also do this with transaction log backups as well. If you use the transaction log backups there are more files to deal with each day, but the nice thing is that you only have to get the full backup once.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: