Hi,
Does anyone have experience automating the Backup/Restore method of refreshing your TEST environment from LIVE? If so, could you provide some direction on how you did it? We're on v11.0.0.3 and SQL '08 R2 with separate PROD and TEST SQL servers.
We currently do it manually but our box office wants to increase the frequency of the refresh to weekly so we'd like to automate it as much as possible. Any guidance is appreciated.
Thanks, Michael J
Metropolitan Museum of Art
Ken McSwain has some scripts that we used and work perfectly with v10. With v11, the only thing I have to do is manually stop the application services before starting the job, and then manually restart them. I have read there’s a way to do that via a remote command line step in SQL Agent, but haven’t had it work successfully for me and haven’t had the time to investigate further.
Nancy Sheleheda
412-456-1387
sheleheda@trustarts.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Michael Johnson Sent: Thursday, December 06, 2012 8:36 AM To: Sheleheda, Nancy Subject: Re: [Tessitura Technical Forum] Automating LIVE to TEST DB Refresh?
Hi David,
That sounds great! I'd very much like to see the scripts when they're finished.
Cheers, Michael J
From: David Frederick <bounce-davidfrederick9045@tessituranetwork.com> Sent: 12/5/2012 3:15:05 PM
Hi Michael,
I am putting the finishing touches on full automation of our test refreshes.
In a nutshell, I have a script that stops necessary services, a fairly large SQL script that performs the restore of the backed up databases and performs all of the rest of the work that needs to be done. Finally, there is a script to bring the various services back up.
I'll try to put this together and post in my file area in the near future so you can take a look. I'll post a link here once it is ready.
Thanks, David
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! ______________________________________________________________________ This email has been scanned by the Symantec Email Security service. For more information please visit: http://www.symanteccloud.com ______________________________________________________________________
Thanks Nancy! I reached out to Ken by email and hope to hear back from him.
Hi Michael
The components of our solution are posted on my Files here.
But I haven't updated the posted scripts with the v11 mods. I'll drag them up and update - hopefully today.
Ken
Thats awsome. Have you been able to post the V11 automated scripts? I am highly interested as well!
Hi All
I've posted the latest version of our refresh script here . (lp_restore_imp_dbs_v3_2012-12-6.sql)
I'm not running that side of the operation any more, so it's my SOH colleague Robert Dunn who has updated the script most recently, specifically for v11, but also some previous updates.
Contrary to advice, he has not needed to add any code in to stop/restart services for v11 - the changes are actually fairly minor. Works fine.
The other job steps that are not in my original posted scripts are
exec sp_changedbowner 'sa'
for both impresario and impresario_cci
For that, Robert's notes say::
The refresh script relies on having a freeware tool called RSScripter installed on the SSRS server. For Model I use a SQL job to run a batch file called TessModel_refresh.bat. Have a look at that and you will see that it references other files. They are all contained in the zip file attached.
Hi Ken,
Thank you so much for posting the script! I owe you a pint at the next conference. ;)
Ps. And thanks to Robert Dunn as well!