Automation - its a blessing and a curse. The curse part is that you want to do it, but who has the time? Of course, the blessing is it will save you time in the long run, but you have to put in the time up-front in order to gain that extra time. :)
I use SSIS daily for my job. I work with the ETL, so automating the data warehouse steps comes naturally. I try to ask each time I do something for someone, whether this is a 1 time thing, or if it will be repeated. I would rather automate something if I can.
Although I am sure this T-SQL Tuesday topic is about administrative type of processes that are automated, I think the best thing we have automated at my job recently, are a set of "checks" at a certain time to make sure everything is as it should be. We are only checking some things, but it has led me to do more. Right now, the checks make sure there is data in certain tables where it should be and it checks certain data in the warehouse to make sure it is within a 5-10% range of yesterday's totals. This automated check has really saved our bacon a couple of times. Before those checks were automated we had to look at a report daily to make sure the data looked ok.
So, back at the main topic. I have recently been learning PowerShell. It is a great tool to automate multiple servers in a single script. My latest automation is to update the DEV database from a copy of the PROD backup file. I get the latest backup filename and location, move the file from the production server to the development server, then restore that with different final data file locations, and finally update users and permissions. It was my first PowerShell script and thank you to Sean @ MidnightDBA for the help!
I know that this year (2014), will be spent learning more about database administration, and more automation scripts will come to be. It is exciting to know that I will feel more in control of the overall SQL environment when I have automated more of it.