August 11, 2014 at 5:50 am
ajiteshmalhotra (8/11/2014)
Hi,I have created the power shell script for database information.Please find the below query:-
ForEach ($instance in Get-Content "C:\temp\test\sqlserverlist.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs=$s.Databases
$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
ForEach ($dbs in $dbss)
{
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=AITSSQL22; Initial Catalog=Workdb; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$name=$dbs.name
$Collation=$dbs.Collation
$CompatibilityLevel=$dbs.CompatibilityLevel
$AutoShrink=$dbs.AutoShrink
$RecoveryModel=$dbs.RecoveryModel
$Size=$dbs.Size
$SpaceAvailable=$dbs.SpaceAvailable
$insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)
VALUES('$Name', '$Collation', '$CompatibilityLevel', '$AutoShrink', '$RecoveryModel', '$Size', '$SpaceAvailable')"
$cmd.ExecuteNonQuery()
$conn.Close()
}
}
But data is not going on database table. Could you please help me out for this.
I am new in powershell.
Thanks
Ajitesh Malhotra
$insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)
needs to be before
$cmd.CommandText = $insert_stmt
Otherwise you have no statement to run. This is because strings are immutable objects that are treated as value types i.e. you assigned the value of $insert_stmt at that time i.e. null.
Hope that helps.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 1, 2018 at 6:33 pm
I have made a simple process and used the Script as SQL Job to populate the Table for historical as well Reporting purpose.
#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside
$params
= @{'server'='MySQLServer1';'Database'='Atul_Test'}
Function writeDiskInfo
{
param($server,$devId,$volName,$frSpace,$totSpace)
try{
if($totSpace -ne 0 -or $frSpace -ne 0)
{
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
} else
{
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=0
$usedSpace = $totSpace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = 0
}}
catch
{ $freePercent=0 }
$InsertResults = @"
INSERT INTO [Atul_Test].[dbo].[DiskDetails](servername,DriveName,TotalCapacity,UsedCapacity,FreeSpace,FreeSpacePercentage)
VALUES ('$SERVER','$devId',$totSpace,$usedSpace,$frSpace,$freePercent)
"@
invoke-sqlcmd @params -Query $InsertResults
}
Function Get-ServerDiskStatus ($SQLInstance)
{
$dp = Get-WmiObject win32_logicaldisk -ComputerName $SQLInstance| Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp)
{
writeDiskInfo $SQLInstance $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}}
#Grab our list of servers, iterate through them and call the function which rights to the database
$Srv = invoke-sqlcmd @params -Query "SELECT ServerName from [Atul_Test].[dbo].[MyServersList]"
foreach ($Instance in $srv){ Get-ServerDiskStatus $Instance.ServerName}
Table Script :
CREATE TABLE [dbo].[DiskDetails](
[servername] [varchar](100) NULL,
[DriveName] [varchar](3) NULL,
[TotalCapacity] [decimal](15, 2) NULL,
[UsedCapacity] [decimal](15, 2) NULL,
[FreeSpace] [decimal](15, 2) NULL,
[FreeSpacePercentage] [int] NULL,
[logDate] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DiskDetails] ADD DEFAULT (CONVERT([varchar](10),getdate(),(112))) FOR [logDate]
GO
Viewing 2 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy