What is the best approach to mirror production data in a test environment

Question

Hi,

     I'm using an SQL 2005 instance to perform a lot of key business operations, and need to mirror my data in test with data from production. Does anyone know what would be the best approach to refresh data in a test environment with data from a production environment using a SQL 2005 instance?

Thanks 

---

Da Chosen One

Answer 1

That depends on if you want a one time data refresh, or a continual refresh.

If you need a one time refresh - a backup restored will work best - or perhaps snapshot replication.

Transactional replication works before if you need something with current data in it.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

I need to be able to refresh my UAT environment like every other month or so. Do you have an step-by-step example of how I can do this?

Thanks

 

---

Da Chosen One

Answer 3

Probably use snapshot replication then. The tables will need to be identical on both sides.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Hi,

I may vote on backup/restore. You can get the benefit of testing your backups which is essential in HA/DR. I used to validate my backups when restoring on dev/uat environments. 

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 5

How do I perform a snapshot replication in SQL server?

---

Da Chosen One

Answer 6

Follow these instructions here. http://www.youtube.com/watch?v=-4OOf3O3HVs

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941