Sql-server – Script file execution through Power shell

powershellsql serversql-server-2005sql-server-2008-r2sql-server-2012

We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell.

Question: is following activity possible through power shell ?

  1. Get the list of selected databases.
  2. Run a script file which may contain DDL and DML scripts on that list.
  3. And results should be saved in text file.

I found this link but this is for all DBs of server but we need execute script on filtered DBs set.

Best Answer

Assuming your or a user for which you have credentials has applicable permissions, it is very easy to run scripts into SQL Server. The simplest method is to use Invoke-Sqlcmd. You can set the -Query parameter to a hard coded value like

Invoke-Sqlcmd -Query "SELECT name FROM sys.databases" -ServerInstance "YourSQLInstance"

Or you can set a variable equal to the value of a .txt or .sql script, using the Get-Content cmdlet.

$sqlQuery= Get-Content -Path "c:\Scripts\MySQLScript.sql"

Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourSQLInstance"

Using this method would allow you to create more complicated scripts. If you tokenized your USE statement, you could do a simple replacement of the value, after you've captured it.

$sqlQuery.replace("xxxDBNamexxx", "YourDBName")

Putting all this together, you could query your instance and capture the required databases, loop through the databases - for each DB read in the required sql script and replace the DB token with the current DB, execute the script.

Logging is another matter, but not too difficult, depending on the method you use. The easiest, might be to just capture the results of the Invoke-Sqlcmd to another variable and write the variable.

$sqlResults=Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourSQLInstance"

Add-Content -Path "c:\Logs\sqlLog.txt" -Value $sqlResults

These are overly simplistic examples intended to point you in the right direction. Someone with more experience might use more advanced methods. It might be worth your while to spend a few hours with the Microsoft Virtual Academy or some other online resource to pick up some basic Power Shell. It's all I've had time for, but even the basics can get you a long way.

Related Question