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*”} |…


Create a stored procedure with SMO

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a stored procedure, we imagine that we have the code of a stored procedure in an embeded resources file. (this sample is written in C# and use a…

2

Make an ADO.Net connection when you have an SMO connection

When you have an SMO connection, it is very easy to make an ADO.Net connection as the connection string is exactly the same. Then you just have to reuse it. Here is a sample that supposed that the object ServerConn is an SMO ServerConnection.   SqlConnection mySQLConnection;   mySQLConnection = new SqlConnection(ServerConn.ConnectionString); mySQLConnection.Open();


Create table with SMO

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a table with a promary key (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0)….


SMO connection

This sample is written in C# and use SQL Server 2005 beta 2 and a beta version of the .Net Framework 2.0. It show how, with SMO you can Connect to SQL Server 2005 In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly): using Microsoft.SqlServer.Management.Smo;…