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
}
}
I have to ask, what resources are you using to pick up Powershell. I keep reading blog posts on it, but I still don’t get it.
Oh, and can’t you enumerate all the SQL Servers on the network using SMO, so you wouldn’t need a text file?
Second question first, yeah, you can, but we’re only moving a few servers at a time and I have to do special backups, enable/disable jobs, check on the last time backups were run, fire off tran log jobs… but only to a limited list of servers. That’s why I load them from the text file.
But, even if you did enum everything, do you really want all the developers local machines to show up in a list of servers? To a large extent, even when dealing with “all” servers, we’re still dealiing with a limited list.
First question; I’m picking up bits & pieces from all over the place. Blogs & articles. I’m also reading through the chapter on PowerShell in Ross Mistry’s 2008 Management & Administration book. I’ve got a copy of Payette’s book (which seems to be the bible to a lot of PowerShell guys). Other than that, a lot of trial & error, like most programming I’ve done. I read a little, code a little, read some more…
I’m currently trying to work with Chad Miller’s PowerShell extensions: http://www.codeplex.com/SQLPSX. They seem to do a lot for you, but I’m having some troubles getting the backup to run.
I’m glad to hear you’re getting some use out of SQLPSX. I’ve heard from a couple of people who have had issues with the baclup function. One problem is with the CopyOnly property. Although Copy Only backups are supported in 2005, SMO 9.0 does not have a CopyOnly property while SMO 10.0 does. Unfortunatley I didn’t catch this in my testing and the current release attempts to set the CopyOnly property. If you are using SMO 9.0, remove the line of script which sets the property.
A second issue I ran into with the Backup class in SMO is that detailed error and informational messages are only exposed through events and Powershell v1 does not directly support events. You can however install a snappin, http://www.codeplex.com/PSEventing, to add event handling and modify the backup script. If you’re interested I have a modified script that uses PSEventing I can post. Another option would be to use Powershell V2 which includes eventing support. I will update the backup function once Powershell V2 is generally available.
If you have any questions, feel free to send me an email.
Hey,
Thanks for the pointers and thanks for all your work on the extension.
I’ll try to get back to doing some work with the backups this week and see if I can resolve the issue. I can’t say I’ll get around to it, although I’m planning on doing more with the extensions every chance I get.