**self-hosted*
Can anyone share with me their process for automating the LIVE to TEST copy?
Right now I have a checklist that I follow to do it manually. Basically I stop the TessService app pool, then blow away the impresario database on our TEST server (right click --> delete). I then execute a database restore using the most recent back up. After that I recreate the logins and then run a series of queries (in a specific order) to do things like change the dbo owner, update defaults, etc.
I do this once a month right now. If I could automate the process I would like to see it occur more often (one a week maybe).
Anybody have any thoughts???
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
Sounds like you might have a few redundant steps in your process -- I would suggest experimenting to see what you can omit or why it's necessary. For example, the services and app pools don't have control over the database, so there's no real need to stop them. You also don't have to delete the test database when you can restore over top of it. Additionally, any series of queries that you run one-by-one can be reduced to one single script execution if structured properly.
I have live-to-test automated down to a single PowerShell script which starts with a COPY ONLY backup of LIVE to a shared folder, puts the test database into RESTRICTED_USER mode to kill any existing sessions and do away with needing to stop/restart any services or app pools, then restores the backup to the test server. Once the restore is done, still in restricted mode, the usual series of scripts are run to synchronize encryption keys, recreate the services user, etc. This includes a custom script to set environment-specific defaults and email profiles (seemingly what Gawain's script is doing), and also includes a TIM-provided script for DropAndRecreateTessituraLogins.sql. Then when everything is ready for access it puts the database back into MULTI_USER mode so that the services can reconnect, and finally it sends an email to hello@ from my account to request a TNEW live-to-test copydown.
I have plans to make this self-service for the box office manager by allowing it to be invoked from a Slack bot... but check back in with me next year on that one.
Wow!
So after reading your reply I found a sample script online:
USE [utilities]
GO
CREATE PROC [maint].RestoreDatabase_{dbname} AS BEGIN
RESTORE DATABASE [dbname] FROM DISK=N'c:\backupfolder\{dbname}.bak' WITH FILE= 1,
MOVE N'{dbname}' TO N'd:\database\{dbname}.mdf',
MOVE N'{dbname}_log' TO N'e:\database\{dbname}.ldf',
NOUNLOAD, REPLACE, STATS= 10,
STANDBY=N'e:\database\ROLLBACK_UNDO_{dbname}.bak'
END
I honestly didn't know that RESTORE DATABASE was a command. Once I get access to those files Gawain sent, I should be able to create something for our environment.
Ashley
Okay so why did I JUST realize I could select this button to get some of the setting in script form:
If I set everything up the way I would manually, then script to query window, I will have at least that part written for me.
Indeed -- none of these tasks are accessible exclusively via the SSMS GUI. You can always find them in the T-SQL reference, which I highly recommend keeping handy: docs.microsoft.com/.../restore-statements-transact-sql