InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:44:19 GMT20http://www.sqlservercentral.com/Forums/Topic1359370-391-1.aspxHi 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,sqlnycTue, 18 Sep 2012 20:53:52 GMTsqlnychttp://www.sqlservercentral.com/Forums/Topic1359370-391-1.aspxYou 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.Tue, 18 Sep 2012 19:28:38 GMTtim_harkinhttp://www.sqlservercentral.com/Forums/Topic1359370-391-1.aspxThanks very much for your replies --SQLNYCTue, 18 Sep 2012 14:29:29 GMTsqlnychttp://www.sqlservercentral.com/Forums/Topic1359370-391-1.aspxYou need to also take care of LOGINS/USERS.Fri, 14 Sep 2012 09:50:56 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic1359370-391-1.aspxThere 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.Fri, 14 Sep 2012 09:34:07 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1359370-391-1.aspxGreetings --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 databases2. compress BACKUPs (could be part of Native SQL Backup)3. FTP compressed BACKUPS to our development environment4. decompress/RESTORE in the development environmentThe 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 --SQLNYCFri, 14 Sep 2012 08:57:28 GMTsqlnyc