Update SQL Server Statistics with Powershell

It can be usefull to have a script to update statistics with powershell. this script is very simple, you just have to connect to you database and call the updatestatistics() method for all your tables : [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null  $s = new-object (‘Microsoft.sqlserver.Management.smo.server’) myServer\myInstance  $db = $s.databases[“myDB”]  foreach ($t in $db.tables) {       write-host “Update stat”  $t.name      …


Rebuild SQL Server index with Powershell

let continue to explore what we can achieve with powershell for SQL Server management. The goal of this post is to rebuild fragmented index with power shell. To achieve that we need to use the invoke-sqlcmd cmdlet. try to use this command, if it doesn’t work, be sure to have all the system module loaded,…


SQL Server backup and powershell

In my previous post, I give some basic sample on how to use powershell for SQL Server. Here we are going to build a sample script to backup database. 1)  Perfom a full backup for a specified database : [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “myServer\myInstance” #Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace…


SQL Server and powershell

Here is some sample of what you can achieve to manager SQL Server with powershell, let start with system script : 1) is the SQL Service running ? Get-WmiObject win32_service | Where-Object {$_.name -match “^*SQL*”} | select SystemName, Name, StartName, State 2) how the process run ? get-wmiobject win32_process | where {$_.name -like “*sql*”} |…


Usefull DMV Query

What are DMV : (Dynamic Management View) View of internals informations. DMV are basically SQL Views on some pretty important internal memory structures. DMV informations include metrics about : indexes query execution operating system Transaction Databases and much more What are DMF : (Dynamic Management Function) As an example sys.dm_exec_query_stats records details of the SQL…


How to get SQL Server 2005 today

Just go here : GO You can get SQL Server 2005 CTP June and Visual Studio 2005 beta 2. Enjoy 🙂