Anyone handling DB maintenance using PowerShell?

blargoe

I've been playing around with some powershell that will backup all of my SQL instances with a single script. I know hardcore SQL guys balk at using PowerShell to manage SQL Server. However, with Powershell, I can use the same code to backup all versions 2000 and higher and can more easily perform file system operations, plus I do not have to enable cmdshell. Also, since I'm redirecting all of my backups to single network location, I can kick off one job and have every instance back up in succession, rather than have to manage schedules on 15+ instances individually to insure I'm not overburdening the network.

Just wondering if anyone had any thoughts on this.

b

RobertKaucher

blargoe wrote: »

I've been playing around with some powershell that will backup all of my SQL instances with a single script. I know hardcore SQL guys balk at using PowerShell to manage SQL Server. However, with Powershell, I can use the same code to backup all versions 2000 and higher and can more easily perform file system operations, plus I do not have to enable cmdshell. Also, since I'm redirecting all of my backups to single network location, I can kick off one job and have every instance back up in succession, rather than have to manage schedules on 15+ instances individually to insure I'm not overburdening the network.

Just wondering if anyone had any thoughts on this.

b

Have you looked at SQLPSX on CodePlex? We do use PoSh for some management tasks but most of our backups are scheduled via Agent. I will use PoSh for adding logins/accounts and doing quick one-off copy backups. I always seem to have a PoSh console open - SSMS is just too heavy to be running all day in conjuntion with VisualStudio...

blargoe

That's on my list to look at... I've been immersed in SQL Server for the past month or so, and now I'm trying to find the best mix of management tools for my environment. I have set up a central management server, a SQLNexus install, and the Performance Dashboard reports, and some quick scripts that query some of the DMV's that I care about. I have more insight into what is going on now than I ever had before. Since most of my instances are Enterprise Edition, I'm also going to look at the MDW.

For the backups, I really like being able to leverage Powershell, because I can easily build in logic to do things such as

- One script for all versions of SQL
- use a UNC path (can do this in Agent, but i was stuck with a different script for my 2000 instances to meet some of my other requirements)
- during disk cleanup check the archive bit on backup files; do not delete backup files from disk if they have not been archived
- pass an argument to specify a tlog backup, but have the script filter out system DB's and DB's in Simple recovery mode (rather than picking and choosing... if I select all user DB's using a maintenance job in 2005 and lower and schedule via Agent, the ones in Simple mode generate an error).

RobertKaucher

SQLPSX makes it much easier than using SMOs. You Can run a select query by the cmdlet Get-SQLData Creating a login is Add-SqlLogin. It is well worth your time!