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

Powershell script to create a dev-base from a live-database

Back when I worked for Promod (one of my first client), the dev team had a wonderful toy : each monday, the database we used for building applications was updated to reflect the last data from the live database.Since then, I have always tried to mimic this feature for every project that I worked on.

The trick was not really pretty : every monday, a database backup was restored from live environnement to the dev one. Then a sometime really long script/sql batch was launched to update the schema (every change that we made to the database had to be put in script in a special folder, and all those scripts were executed every monday)

This week, I really wanted to get this thing on a new project but the vbscript-based tool I've used many times before didn't worked for some reason, so I have rewritten the whole thing with Powershell.

The script is split in a few parts :

• first we have to ensure that no one is locking the database. The dirtiest of tricks is used here : I just shutdown and restart SqlServer, remembering to restart all the dependent services (such as Sql Server Agent) that were running before the restart

 $depserv = (get-service mssqlserver).DependentServices 
              | ?{$_.Status -eq 'Started'}
 restart-service mssqlserver -force
 $depserv | start-service

• a restore database is launched thru osql [Updated 05-mar-2007 : As many of you may have guessed, the actual copy of the .bak from the backup server should be done before restarting sqlserver - I didn't put it before in this post only for readability.]

copy \\backupsrv\live\newproject.bak d:\dev\newproject.bak
osql -E -Q "RESTORE DATABASE NewProject from disk=
      'd:\dev\newproject.bak' with replace"
del d:\dev\newproject.bak

• a Team Foundation Server-based refresh of the script directory is then triggered

&($env:tfpath) get newproject\datascripts /r

• for each .sql file in the aforesaid directory, powershell just run osql with this file

 dir ($env:scriptdir)+"\*.sql" 
     |% {osql -E -d NewProject -i $_.FullName}

With less than 10 lines (I had to define the scriptdir & tfpath variables, and to add a little error handling), this script replace an about-150-lines vbscript file, that's pretty amazing !

Visualiser les commentaires(RSS) ou en Ajouter