August 14, 2024 at 9:33 am
I have 5 servers in the servers table . The code is not looping through all servers. Its only going to the ABC server 5 times and capturing data from ABC server 5 times.
$Servers = Invoke-Sqlcmd -Query "SELECT ServerName FROM dbo.Servers" -ServerInstance "ABC" -Database "DB"
foreach ($Server in $Servers)
{
$ServerName = $Server.ServerName
If (Test-Connection -ComputerName $ServerName -Count 1 -ErrorAction silentlycontinue)
{
Invoke-Sqlcmd -Query "INSERT INTO [DB].[dbo].[SQLBackups] ([ServerName]
,[DatabaseName]
,[RecoveryModel]
,[LastBackupDate]
,[BackupType]) SELECT @@servername as ServerName, bk.database_name AS Name,
bk.recovery_model AS RecoveryModel,
bk.backup_finish_date AS LastBackupDate,
case when bk.Type = 'D' then 'Full Backup' end as Type
--bk.backup_size AS LastBackupSize
FROM msdb.dbo.backupset AS bk
INNER JOIN (
SELECT database_name,
MAX(backup_finish_date) AS LastBackupDate,
Type
FROM msdb.dbo.backupset
GROUP BY database_name,Type
) AS bkmax
ON bk.database_name = bkmax.database_name and
bk.backup_finish_date = bkmax.LastBackupDate and
bk.type = bkmax.type
and bk.Type = 'D'
and bk.backup_finish_date < getdate()- 1;" -ServerInstance "ABC" -Database "DB"
}
else
{
Invoke-Sqlcmd -Query "INSERT INTO dbo.FailedConnections (ServerID, TestTime, Success,ServerVersion,InstanceName) VALUES ($ServerID,SYSDATETIME(),0,@@version,@@servername);" -ServerInstance "ABC" -Database "DB"
}
}
Thanks
August 14, 2024 at 9:44 am
Possibly because of this row:
and bk.backup_finish_date < getdate()- 1;" -ServerInstance "ABC" -Database "db"
Try changing it to
and bk.backup_finish_date < getdate()- 1;" -ServerInstance $ServerName -Database "db"
August 14, 2024 at 10:07 am
I tried that...
now I receive this error....
Invoke-Sqlcmd : Cannot open database "DB" requested by the login. The login failed.
Login failed for user 'Domain\User'.
August 14, 2024 at 12:09 pm
So you want to loop around 5 servers, pull the backup information, then store that data in ABC server in database DB?
August 14, 2024 at 12:18 pm
yes
August 14, 2024 at 12:28 pm
Then you will need to change your approach.
You'll need to store the output of the select into an object, then open another connection back to ABC - DB and do the insert.
Or use linked servers and use 4 part naming and change the INSERT INTO DB to be INSERT INTO ABC.DB.dbo.whatevertable
Viewing 8 posts - 1 through 8 (of 8 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