The Multifunctioning DBA

May 29 2009   2:49PM GMT

Powershell SQL Load Script

Colin Smith Colin Smith Profile: Colin Smith

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.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: