Moving production database to test database3 every days.

Question

Moving production database to test database3 every days.

Rem70Rem

SSCertifiable

Points: 6166
More actions
February 28, 2014 at 1:02 pm

#304684

Hi,
What is the best way to move a Production database to a test one ever days after processing is done ?
I am thinking about backup/restore as the simplest way. Will you recommend log ship or replication?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

I would go with the Backup/restore route as well. This has the benefit that nothing that happens in your test environment can have an effect on your production environment. And, you can make data (or even ddl) updates in your test environment.

My biggest problem would be with the schedule. You will almost certainly have this scripted out, but it will almost equally certainly go off in the middle of some critical testing round.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Rem70Rem (2/28/2014)
Hi,
What is the best way to move a Production database to a test one ever days after processing is done ?
I am thinking about backup/restore as the simplest way. Will you recommend log ship or replication?
Thanks

Best approach would be Backup\restore on a scheduled basis.

--

SQLBuddy

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Rem70Rem (2/28/2014)
Hi,
What is the best way to move a Production database to a test one ever days after processing is done ?
I am thinking about backup/restore as the simplest way. Will you recommend log ship or replication?
Thanks

Do you need the test database read only or read\write?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 4

Hi,

The "test" version needs to have the same right as the production one, so read/write.

Thanks all for your help.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Rem70Rem (3/3/2014)
Hi,
The "test" version needs to have the same right as the production one, so read/write.
Thanks all for your help.

If the test DB is R\W, You can't use LShipping\DB Mirroring. Replication can be used but it's good for object level replication.

Backup\Restore would be best for this situation ..

--

SQLBuddy

Steve Jones - SSC Editor SSC Guru Points: 735912 More actions · Answer 6

Backup/restore for me as well.

I might script this as a daily process that moves the latest backup from production to test, but doesn't necessarily restore it. This would make it fast if you need to ad hoc restore any day.

barsuk SSChampion Points: 12280 More actions · Answer 7

We have similar process, when we copy full backup on the weekend and diff backup(s) on the weekdays...

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 8

We have several databases that the apps folks want refreshed on a scheduled basis. Some weekly and some monthly. Just create a backup job and schedule it and then a restore job to restore it along with all of the steps to recreate users access to the test db.