SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ways to refresh development environment


ways to refresh development environment

Author
Message
sqlnyc
sqlnyc
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 913
Greetings --

I'm trying to not reinvent the wheel here, and could use some help from those of you that might have run into something like this.

We are running SQL 2008 R2 Standard.

Our company will be moving to a new production server in October. I have been tasked with figuring out a solution for refreshing our development environment from production.

A top level view of what needs to be done:

1. BACKUP databases
2. compress BACKUPs (could be part of Native SQL Backup)
3. FTP compressed BACKUPS to our development environment
4. decompress/RESTORE in the development environment

The production server is available only through a VPN.

At the moment, the total compressed size of all backups is ~70GB, and is expected to grow.

The databases contain sensitive financial information, all of which will be deleted after the RESTORE. I had suggested investigating log shipping, but this won't fly, as they don't want to store the sensitive data on site, as would be the case using WITH STANDBY.

Since there are dependencies, i.e. we don't want to FTP until the BACKUP is completed, and we don't want to RESTORE until the FTP has completed, I think that some form of encapsulation is required.

Perhaps a Powershell solution might be possible, but before going down the road too far, I wanted to see how others have solved this issue.

Thanks in advance --

SQLNYC
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38110 Visits: 9274
There are any number of FTP utilities you can use to accomplish this. Once you get the file onto the dev environment, decrypt it and put it into another location (by way of saying encrypt the backups before you move them between environments).

Then have a SQL Server job do a check to see if the file exists in that directory. The file check job kicks off the restore job and the restore job contains a data scrubbing step, a permissions update step, and any other steps you may need.

The best way to do this is schedule the backup sending at a certain time each day. Then give yourself some cushion (so as the backups grow the job won't fail by trying to lock onto the file while the file is locked by the decrypting / moving process). At that point, have a recurring job that checks for the file every X minutes within a Y hour timeframe. We use every 30 minutes over a 4 hour time frame. This gives the files a chance to decrypt properly.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7525 Visits: 2250
You need to also take care of LOGINS/USERS.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
sqlnyc
sqlnyc
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 913
Thanks very much for your replies --

SQLNYC
tim_harkin
tim_harkin
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 925
You also may want to consider using the WITH COPY_ONLY for the backups used to refresh dev, so you do not mess up the transaction log sequence of your primary backup\restore process.
sqlnyc
sqlnyc
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 913
Hi Tim,

Thanks for your reply - we will be using FTP to copy the regular nightly backup to our dev server, so no need to use COPY_ONLY. But thanks for suggestion.

Best wishes,

sqlnyc
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search