November 30, 2021 at 8:09 pm
Hello experts,
As has been requested of countless DBAs, I have been asked to do a weekly refresh of a dev database from production. I have several scripts that do this in manual fashion, but once and for all I would like to develop a truly automated process that I could put into a SQL Agent job.
To that end, are these the general steps I should incorporate?
It seems so straightforward in principle, but invariably I get odd errors like that the db is currently in use, etc.
I suppose I could decompose this into 6 (or however many) job steps too, to make it more modular and easier to debug?
Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 1, 2021 at 1:37 pm
It's really just a question of breaking down the steps, as you have, and then implementing them one at a time.
One step you are missing though is cleaning the data. Production data should never be given straight into non-production environments. So, you'd want to add a step to either clean the data before you bring it back online, or, restore it first to a different server and then clean it there, backup that and restore to all non-production environments.
Quick story. I used to, per business requirements, simply hand production data to developers. One developer, while testing email while simultaneously behaving inappropriately, managed to send adult themed images to our entire client base. Never, ever, let another production database into development without some clean up after that.
As for the database being in use, Devs should be able to control everything they need to, without being 'sa' or 'dbo'. Take that away and then simply set the database to restricted user with rollback immediate. That'll get the job done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2021 at 1:39 pm
I put dev in read-only restricte mode ( with rollback immediate ) to force everybody out, before attempting to restore.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply