Moving Database from one server to another every night and restoring it with overwrite

Question

Post reply

Moving Database from one server to another every night and restoring it with overwrite

gttred

SSC Enthusiast

Points: 103
More actions
January 7, 2011 at 11:22 pm

#146746

Hi.
I have been assigned to copy all the data from the production databases to the test environment every night. I would really appreciate your helps.
Thank you,

Viewing 7 posts - 1 through 6 (of 6 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Please check if these scripts help.

http://www.mssqltips.com/tip.asp?tip=1584

http://www.sqlservercentral.com/scripts/Backup+and+Recovery/63071/[/url]

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 2

You have a lot of options. We do this at work just using the nightly backup to perform a restore. It does two things. It gets us that test environment you're talking about and it validates the backups.

But you could also look into replication. If you set up transaction replication you could have a constantly updating test machine. The cost is you have to set up & maintain replication.

You could also look into log shipping, capturing the transaction log and moving it over to the test server.

Personally, based on what you need, I'd go with that. You may also need to add a script to clean out any sensitive data and, assuming your devs & testers don't have access to production, you'll also have to have a script to add some security to grant them access to the server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning