• Replies 11 replies
  • Subscribers 340 subscribers
  • Views 123 views
  • Users 0 members are here
Related

Automating LIVE to TEST DB Refresh?

Michael Johnson
$organization

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

  • $organization

    We use the backup/restore method too and still do it manually.  Looks like it’s going to become a little more time consuming in v11!  So I’d be interested in automation ideas as well.

    Thanks,

    …Dave

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Michael Johnson
    Sent: Wednesday, December 05, 2012 8:57 AM
    To: Vivino, David
    Subject: [Tessitura Technical Forum] Automating LIVE to TEST DB Refresh?

     

    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




    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!

  • $organization

    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 

  • $organization in reply to David Frederick

    Hi David,

    That sounds great! I'd very much like to see the scripts when they're finished.

    Cheers, Michael J

  • $organization

    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
    ______________________________________________________________________

  • $organization in reply to Nancy Sheleheda

    Thanks Nancy! I reached out to Ken by email and hope to hear back from him.

  • $organization in reply to Michael Johnson

    Hi Michael,
    Definitely, all the steps has to be implemented in SQL scripts , for instance modification of Environment-specific values in Test after the DB restoration from Live backup.

    If interested, to preserve the Environment-specific values I use a separate database (a temporary storage) to where I copy Environment-specific tables  before moving the DB from Live to Test:
    T_DEFAULTS
    TR_CUSTOM_TAB
    GOOESOFT_REPORT
    GOOESOFT_APPLICATION
    TR_PAHT_CONFIGURATION
    TX_USER_GROUP
    TX_MACHINE_LOCATION

    After the DB restoration I use these stored tables to restore old Test data.

    Also, I am attaching a couple of scripts I use:
    - RESTORATION OF DB PRIVILEGES for Direct DB USERS(DB DEVELOPERS) ;
    - FIXING OF ORPHANED DB USERS.

    Thanks,
    Simon Basyuk, DBA of Carnegie Hall
    sbasyuk@carnegiehall.org

  • Former Member
    Former Member $organization in reply to Michael Johnson

    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

  • $organization in reply to Former Member

    Thats awsome.  Have you been able to post the V11 automated scripts?  I am highly interested as well!

  • Former Member
    Former Member $organization in reply to John Brandt

    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

     

    • Two steps to ensure that the db owner is set correctly - ie run

     

    exec sp_changedbowner 'sa'

    for both impresario and impresario_cci

     

    • Run a batch file to copy SSRS reports across.

     

    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. 

     

    Ken

     

     

  • $organization in reply to Former Member

    Hi Ken,

    Thank you so much for posting the script! I owe you a pint at the next conference. ;)

    Cheers, Michael J

  • $organization in reply to Former Member

    Ps. And thanks to Robert Dunn as well!