• 2 Powershell functions for SMO (Sql Server)

    In the last few month, I often had to execute management operations on Sql Server 2005. While using SQL scripts with SQLCMD is nice (I suppose), my growing fondness for Powershell made me write a few scripts that you may find interesting.

    The first on is rather simple and will return the SMO object for a non-system database (or multiple database if you use a wildcard).

    function get-database($name = "*")
{
[System.Reflection.Assembly]::LoadWithPartialName
           ("Microsoft.SqlServer.Smo") | out-null
$_srv=New-Object 
           "Microsoft.SqlServer.Management.Smo.Server" "(local)"
return $_srv.Databases | ?{$_.IsSystemObject -eq 0 -and $_.Name -like $name}
}

    A note of advice for powershell scripters here : if you ever do such a wonderful trick as loading an assembly, remember to send the result of your call to Load/LoadWithPartialName to out-null. If you don't do so, these methods will push a AssemblyInfo in the pipeline.

    The second script is not really more difficult : it backups a database (or all databases). It should not be used as is (except for small servers with simple databases) in a live environment, but may help you build a more complex backup script if needed.

    function backup-database($dbName, $rootFolder)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
                   | out-null
[System.IO.Directory]::CreateDirectory($rootFolder) | out-null
$_srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)" 
$_bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"
$_bck.Action = 'Database' 
    $_fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$_fil.DeviceType='File'
$_fil.Name=[System.IO.Path]::Combine($rootFolder, $dbName + "-"
                             + [DateTime]::Today.ToString("yyyy-MM-dd")+".bak")
$_bck.Devices.Add($_fil)
$_bck.Database=$dbName 
$_bck.SqlBackup($_srv)
write-host "Backup of " $dbName " done"
} 

function backup-alldatabase($rootFolder)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
                  | out-null
$_srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"
$_srv.Databases |
                  ?{$_.IsSystemObject -eq 0} |
                  %{backup-database $_.Name $rootFolder}
}

    Visualiser les commentaires(RSS) ou en Ajouter