Powershell Blog Worth Reading

May 14, 2009 at 2:55 pm (PowerShell) (, , )

I don’t usually make a big deal about my blog roll. I usually add either people I read regularly or people who ask to be added. I don’t have a hierarchy of belonging & all that (yet). But I want to point out one that I just recently put up. Chad Miller, creator of SQL Server PowerShell Extension, blogs about SQL Server & PowerShell. If you’re not already reading his stuff, you should. Unlike me, he really knows what he’s talking about.

Permalink Leave a Comment

More PowerShell Basics

May 14, 2009 at 9:22 am (PowerShell, SQLServerPedia Syndication) (, , , , )

Because of the data center move, we have a number of different functions that we’re running on totally different sets of servers on different days. None of this is part of our normal maintenance routines, so I’ve been using it as a great opportunity to stretch a little with PowerShell. I’m still learning, a lot.

The latest task was to get the databases of a list of servers backed up. I initially tried it using SMO. It works great. But it’s serial across all the servers as well as across the databases. I’m fine with serial backups on the databases (very, very fine, I saw a parallel backup of all databases once, pretty sparks, like the Fourth of July) but I really wanted all the servers to get backed up in parallel. So I tossed the idea of having PowerShell run the backups, but I did have PowerShell generate the backup scripts for me.

Again, this is simplistic, I know, but I’m really just getting started. I went to a switch instead of the if conditional for more of an excercise than anything. The last script I posted I’ve updated to a switch, but this one should still be an if, but hey, sue me.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($svr in Get-Content "c:\scripts\serverlist.txt")
{
 $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
 
 $query = "SELECT bmf.physical_device_name ,bs.database_name FROM msdb.dbo.backupmediafamily AS bmf JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id where bs.backup_start_date > GETDATE() - 1 AND bs.[type] = 'D'" 

 $table = Invoke-Sqlcmd -Query $query -ServerInstance $svr 

 foreach($rows in $table)
{
  $path = "c:\scripts\bu_" + $svr.Replace("\","_") + ".sql"

  switch ($srv.Information.VersionMajor) 
  {
   10 $val="backup database [" + $rows["database_name"] + "] to disk = '" + $rows["physical_device_name"] + "' with compression"}
   default{$val = "master.dbo.xp_backup_database @database ='[" + $rows["database_name"] + "]', @filename ='" + $rows["physical_device_name"] + "', @threads = 1"}Add-Content -path $path -value $val
  }
 Add-Content -path $path -value $val
}
}

Permalink 4 Comments