PowerShell has a module to assist with this, you can find out all the specifics at https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module. I was asked to pull data and export it to a CSV to save time, which is easy to do with Task Scheduler and PowerShell. Once you have installed the SqlServer module the website mentioned above will help with that but if you don’t want to read it i’ll make it simple. Install Windows Remote Management version 5+ (I used version 5 at the time 5.1 is out at the time of writing this). You can download version 5 from here. run the following command in an elevated powershell:
Install-Module -Name SqlServer
Once you have done that all you need to do to pull data out of the MSSQL server is
$sqlData = Invoke-Sqlcmd -Query "Your sql query" -Database "Your_Database" -Server "ServerName"
Now to save that data to a CSV file we do:
$sqlData | Export-Csv -NoTypeInformation -Path "Where to save" -Enocoding UTF8
Now all that is left is to crate a Scheduled Task which I will not be covering but will include how to call PowerShell properly in the action tab.
Program/Script: powershell.exe
Add arguments: -ExecutionPolicy Bypass full_path_to_script (surround the path in quotes if it contains spaces)
Complete script:
#Requires -Module SqlServer
$sqlData = Invoke-Sqlcmd -Query "Your sql query" -Database "Your_Database" -Server "ServerName"
$sqlData | Export-Csv -NoTypeInformation -Path "Where to save" -Enocoding UTF8