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.