How to get list of databases from multiple servers
“Select name as [database name] ,@@servername as [server name]from sys.databases”
But what if some one asks you to get list of databases from 100+ servers. It will take days to finish manually but powershell can get this done in a click
So how to do this
- First find a server in domain having powershell installed
- Create directory “Drive:\Monitoring_Automation\”
- Create files in above directory
b. Powershell script
c. Batch file to call PS1 file
d. Output file to collect list of database
- 4. In “Drive:\Monitoring_Automation\Servers.txt” write all server name separated with enter from which you want to get database list
- 5. In “Drive:\Monitoring_Automation\ dblist.ps1” place below code
/*********** Code Start****************/
#File name : Drive:\Monitoring_Automation\dbdetail.ps1 #Initializing output path
#$FilePath = "Drive:\Monitoring_Automation\"
#$OutFile1 = Join-Path -path $FilePath -childPath ("dblist_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")
$OutFile = "Drive:\Monitoring_Automation\output.csv"
#Running code on each server
foreach ($svr in get-content "X:\Monitoring_Automation\Servers.txt"){
$conn = "server=$svr;database=master;Integrated Security=sspi"
$cmd = "select name , @@servername from sys.databases"
$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $conn)
$DataTable = new-object System.Data.DataTable
$DataAdapter.fill($DataTable) | out-null
$svr
$DataTable | Format-Table -autosize
#$DataTable | export-csv -noType $OutFile1
$DataTable >>$OutFile
}
#$DataTable | export-csv -noType $OutFile1
$end = get-date
write-host "End: " $end
/*****************ENd of Code***********************/
- 6. In “Drive:\Monitoring_Automation\ GetDBlist.bat” place below code
Powershell.exe “ Drive:\Monitoring_Automation\dbdetail.ps1”
/************* CODE END *******************/
- 7. If you will not even create “Drive:\Monitoring_Automation\output.csv” , powershell will take care of step
- 8. Now open command prompt : Run -> CMD -> Go to Drive:\Monitoring_Automation\
9. Once execution is completed, you will get output in command prompt screen and in “Drive:\Monitoring_Automation\output.csv” as well.
- 10. Copy the output in excel , use data -> text to column option to format and your report is ready to be sent
No comments:
Write commentsPlease do not enter spam links