October 28, 2013 at 4:24 am
Dear all,
I just wanted to ask you for some tipps on my following task:
I have one productive SQL Server 2008 R2 with a couple of databases running on it. Now I need to figure out a way to regularily transfer the DBs from the productive server to the SQL Server 2008 R2 running in the test environment.
Once I had to solve a similar case with a SQL Server 2005 and I did it the following way:
- backup databases from productive server with SQL agent
- xcopy backup of the databases to test environment
- restore backed up productive databases on test server with sql agent
Well basically that worked pretty good, but I think that there might be a better way to solve this.
We are okay if the databases on the test environment are updated only once a day, that would be better than a live replication which impacts the performance on the productive system during the day.
Maybe someone of you got a tipp or hint for me!
Thank you in advance!
October 28, 2013 at 5:19 am
In your test enviornment does the data need to be read \ write or can it be read only?
MCITP SQL 2005, MCSA SQL 2012
October 28, 2013 at 7:16 am
RTaylor2208 (10/28/2013)
In your test enviornment does the data need to be read \ write or can it be read only?
Hi RTaylor,
we would like to be able to read and write the databases on our test environment!
Thank you for your time!
October 28, 2013 at 7:32 am
Your options are very limited in that scenario.
1. You could keep the enviornments in sync using a custom ETL solution ran on demand to make all the changes but this would be rather complicated.
2. Use CDC keep your test enviornment up to date with production
3. Automate synchronisation of your data using redgate data compare.
All 3 options are not ideal so backup restore is most likely best suited to your needs.
MCITP SQL 2005, MCSA SQL 2012
October 28, 2013 at 8:28 am
Hi RTaylor,
thank you for the tipps - I think I will stick with SQL Agent for this task!
Just found a nice script to query the master database for all existing databases and backup those, maybe that somes handy for someone reading this topic:
http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/%5B/url%5D
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply