Script file execution through Power shell

Question

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.

• Tom V: How will you be specifying the list of databases that it needs to be executed on?
• AA.SC: i will use 'select name from sys.databases where name like '%_ProductionDB' '

Answer 1

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.

• user507: Only additional thing I would note on using Invoke-Sqlcmd is that it has a known problem with long queries timing out, even if you set the query timeout to unlimited. This is one reason why folks will move toward using System.Data.SqlClient or SMO to execute queries. Which a note on using either of the latter options is SMO will accept the GO terminator, where SqlClient does not.
• AA.SC: @ShawnMelton what is the time out period ? and how long scripts are problematic through this process is there any limit on file size?
• user507: If you are using the cmdlet on SQL Server 2012 or higher, it is supposedly fixed: connect.microsoft.com/SQLServer/feedback/details/551799/…
• AA.SC: How about 2005?
• user507: @AA.SC SQLPS does not exist for SQL Server 2005. You can install SQLPS to use against a 2005 instance if desired.