Powershell script to re-start SQL Server services for multiple servers

Step1: Create folder D:\Test\
Step2:Open notepad and add all servers one after one as per below in "D:\Test\ServersList.txt" file
Server1
Server2
Server3
...etc

Step2: Create Test.ps1 powershell script file with below code in "D:\Test" folder
D:\Test\Test.ps1 file code:

Get-Content D:\Test\ServersList.txt | % {

 $Server=$_

 Write-Host  "Server Name " : $Server

If(Test-Connection $Server -Quiet)
{

 $Srv=Get-Service -Name MSSQLSERVER -ComputerName $Server

  If ($Srv.Status -eq "Running")
   {
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

    } Else {
      Write-Host  "SQL Server Service not running"
    }
 } else {
      Write-Host  "Failed to connec to servers:" $Server
  }| Export-Csv D:\Test\FailedConnLog.txt -NoTypeInformation
} | Export-Csv D:\Test\OutputLog.txt -NoTypeInformation


OR
$SList= Get-Content "D:\Test\ServersList.txt"
ForEach ($Server in  $SList)
{
  Write-Host $Server
#### Write code here ######
}

Command to execute or schedule in job:
D:\\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\Test.ps1

SQL Server 2016 -- New features

New Features in SQL Server 2016
------------------------------------------------
Always Encrypted
Dynamic Data Masking
JSON Support --Java Script Object Notation
Multiple TempDB Database Files
PolyBase
Query Store
Row Level Security
In-database R language support: Microsoft acquired Revolution Analytics
       --R Comes to SQL Server-- advance analytics Big data & SQL Server
Stretch Database
Temporal Table
In-Memory Enhancements --supporting foreign keys, check and unique constraints and parallelism.                                               -- Tables up to 2TB are now supported.
Parallel Data Warehouse Appliance, for more easily managing relational and non-relational data
Enhanced server management for Master Data Services
Enhanced hybrid backup to Azure and faster restores to SQL Server in Azure virtual machines
Improvements on AlwaysOn Availability Groups:
  --3 sync replicas, up from 2 in SQL 2014.
  --Listener will be able to do round-robin load balancing of read-only requests on multiple secondaries
Improvements on Columnstore Indexes



Additional details: sql server 2016 Features

CSV file data load into SQL Server table Using BCP and schedule job

Using BCP, CSV file data load into SQL Server table

--1.Create table "BCPDataLoad" with required columns
example:
CREATE TABLE BCPDataLoad
(
id int,
Name varchar(20)
);

2.LoadData.txt
--Loadable data keep in "E:\Data\LoadData.txt" file
--data should be id,name columns wiht comma separately
example
id,name
11,aaaa
22,bbb
33,ccc

3.Create BAT file as "BCPLoad.bat" with below
(OPen notepad and copy paste below script and save as
 file name: BCPLoad.bat
 save as type: allfiles

--BCPLoad.bat
set log=E:\Data\BCPLoad_Log.txt

echo %date%. %time% before BCP data load >>%log%

REM  change the Database_Name" below as your actual database name

bcp Database_Name.dbo.BCPDataLoad_Name in E:\Data\LoadData.txt -c -t, -S ServerName -T >>%log%

echo %date%. %time% After BCP data load >>%log%

4.Schedule job using SQL Server agent job or any 3rd party tools for "BCPLoad.bat" file

OR
Manually run BAT file: You can run manually "BCPLoad.bat" file,
   go start ,right click run "cmd" enter,cd "E:\Data" (go to e drive with data folder)
   in commandline window E:\Data\BCPLoad.bat hit enter, then the data load will finish.

5.Check the logs in "E:\Data\BCPLoad_Log.txt" location
6.Check the data load in "BCPDataLoad" table
   select * from BCPDataLoad

output : should be
id,name
11,aaaa
22,bbb
33,ccc

Batch script -- using FOR loop for multiple servers list to run SQL script

1.Save all servers in "Serverlist.txt" file in "E:\batch" folder.
example: Serverlist.txt
server1
server2
server3

2.Save "sqlquery.sql" file in "E:\batch" folder

3.Save below script as Test.bat in "E:\batch" folder

set log=E:\batch\Test_log.txt
echo start for loop >>%log%
for /F "tokens=*" %%S in (E:\batch\Serverlist.txt) do (
e:\ give here sqlcmd location\sqlcmd -E -S %%S -i "E:\batch\sqlquery.sql"  -s "," -h-1 -W >>"E:\batch\output.csv"
IF NOT ERRORLEVEL 0 GOTO :ERR
)
echo End for loop>>%log%
echo !Success. sqlcmd ok. >>%log%

GOTO :EOP

:ERR
echo !Error. sqlcmd failed >>%log%

:EOP

4.Output will store in "E:\batch\output.csv" file.

Note:Include SET NOCOUNT ON and SET NOCOUNT OFF in your "sqlquery.sql" to avoid info message in your output.

Poweshell/Batch script execute using sql agent job

Using Batch file 
----------------
1.Create D:\Test\TestDBFile.bat file

set log=D:\Test\TestDBFile_Log.txt

Echo %date%.%time%.... Before  rename>>%log%

Rename D:\SQLDATA\SQLDAT\Test.mdf TestOld.mdf >>%log%
Rename D:\SQLLOG\SQLLOG\Test_log.ldf TestOld_log.ldf  >>%log%

Echo %date%.%time%.... After  rename>>%log%

First test "TestDBFile.bat" execute , through command prompt,
if it working then schedule sql agent job( for sql agent job drop down select "Operating System(CMDExec)" option from step in sql agent.
command (give):D:\Test\TestDBFile.bat

2.Using Powershell 
-------------------------

1.Create D:\Test\test.ps1 file with below details

Rename-Item D:\SQLDATA\SQLDAT\Test.mdf TestOld.mdf ;
Rename-Item D:\SQLLOG\SQLLOG\Test_log.ldf TestOld_log.ldf;

2. You can execute with below:
D:\fullpath\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\test.ps1

3.You can schedule sql agent job.


OR

3.Powershell other way using batch script

1.Create D:\Test\test.ps1 file with below details

Rename-Item D:\SQLDATA\SQLDAT\Test.mdf TestOld.mdf ;
Rename-Item D:\SQLLOG\SQLLOG\Test_log.ldf TestOld_log.ldf;

2. Crete D:\Test\TestPowershellscript.bat file

set Pslog=D:\Test\TestPowershellDBFile_Log.txt

Echo %date%.%time%.... Before  rename file using PS>>%PSlog%
D:\fullpath\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\test.ps1

Echo %date%.%time%.... After   rename file using PS>>%log%

3.First test "TestPowershellscript.bat " execute , through command promt,
if it working then schedule sql agent job( for sql agent job drop down selact "Operating System(CMDExec)" option from step in sql agent.
command (give):  D:\Test\TestPowershellscript.bat