extract data from all servers and dump it into a table

Question

extract data from all servers and dump it into a table

SSCrazy Eights

Points: 9212

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

This topic was modified 3 months, 1 week ago by mtz676.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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"

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

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'.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

mtz676 wrote:

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'.

Seems like a reasonable error message ... not sure how it can be made any clearer.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 4

there is no DB database on all the 5 servers...the database is only on ABC server....

This reply was modified 3 months, 1 week ago by mtz676.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

So you want to loop around 5 servers, pull the backup information, then store that data in ABC server in database DB?

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 6

mtz676

SSCrazy Eights

Points: 9212

August 14, 2024 at 12:18 pm

#4444845

yes

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

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