This is probably a simple process, but I don't know the most efficient way to handle it.
We have a database that is restored each morning from a production backup (the database is used to trouble-shoot production problems in an exact replica of our production database.)
There is some sensitive information we do not want available in this trouble-shooting database, so we created a stored procedure that encrypts the columns. The stored procedure does not exist on the production database, so when the "fix" database is created, the stored procedure doesn't exist there either.
Is the best way to create the stored procedure to add the actual SP script directly into a step in the restore job? And then the next step would execute the newly-created SP? Or is there a more elegant way to create that procedure within the restore job?
Open to suggestions!