In my environment we have some servers, test servers, that are loaded from the Production Servers on a regular basis. Since this is the case I have decided that I would like to attempt to script this. I just started but I am already well on my way to getting this done. I am pretty sure that I will be able to do what is needed here, I just need to take it slow and work through the issues as they arise.
So far I have been able to have the script list all the instances that may need to be loaded and ask what instance you would like to load. I select the number next to the instance, try to avoid typing errors, and the script then determines what host this instance needs to be loaded from. So essentially I have done all of my variable setup at this time. Here are the rest of the steps that I have in mind for this script.
Once I know the host and the destination servers I will connect to the destination and delete any backups that I might have on disk. I will not need them anymore since I am loading from Prod anyway. I will then check to find out the amount of free space that I have on the disk and compare that to the size of the production backup file. If I have room then I will copy the prod backup to the test host. Once that is complete I will lock all logins, kill any spid in the target DB, and then load the DB. After that is complete I will have to deal with users in the DB and making sure that I can match them up with the appropriate logins. Then I will make the DB available to users again by enabling the logins.
Simple enough plan but I am sure that I will run into some hurdles along the way. Should be a fun script to do though. Any thoughts or ideas about this script please let me know.