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

Red Gate Crib Sheet Compendium

January 13, 2009 at 11:19 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , , , )

Red Gate has compiled a bunch of it’s Cribsheets into a single E-book, the SQL Server Cribsheet Compendium. It’s pretty cool. I’ve got two entries in there, performance tuning and backups & restores, along with great articles from Robyn Page, Phil Factor, Robert Sheldon and Amirthalingam Prasanna, pretty heady company. It’s worth a look.

Permalink Leave a Comment