Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

2008 Central Management Server - Schedule Multi Server Query? Expand / Collapse
Author
Message
Posted Friday, December 9, 2011 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 12:01 PM
Points: 7, Visits: 131
Hey Howard, I can create job easily - Job creation is not a problem, I did execute the script you sent me, it did create the job but when I run the job, I get error on Powershell script. Same error as I mentioned before. Did you test exactly this powershell script on your machine?
Post #1219540
Posted Friday, December 9, 2011 9:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 298, Visits: 907
muhammadrazzaqpk-1032285 (12/9/2011)
Hey Howard, I can create job easily - Job creation is not a problem, I did execute the script you sent me, it did create the job but when I run the job, I get error on Powershell script. Same error as I mentioned before. Did you test exactly this powershell script on your machine?


Yes, just tried it again and it runs correctly.

Run this manually on the CMS to make sure that the server list matches what you expect.

SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id

Also be sure that the SQL Agent service account on your CMS has access to all the servers. It runs as the SQL Agent ID on the CMS unless you set up a proxy.

Post #1219550
Posted Friday, April 27, 2012 9:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:54 AM
Points: 457, Visits: 258
this work worked like a charm. This is the one I am looking for. Thanks a lot.
Post #1291639
Posted Saturday, December 29, 2012 11:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 8:27 AM
Points: 19, Visits: 112
Hi All every thing is working fine.....
i got the output also. But i want servername also in the output file .
Pls help.
current output is:
name, Backup_Type, LastSuccessfulBackup, IntervalInDays
I want it should be:

Server_Name, name, Backup_Type, LastSuccessfulBackup, IntervalInDays


  Post Attachments 
Current output.JPG (10 views, 15.93 KB)
Post #1401230
Posted Wednesday, January 2, 2013 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:54 AM
Points: 457, Visits: 258
Add @@servername in the query. It will give the server name in the output results.
Post #1401928
Posted Tuesday, March 19, 2013 1:50 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:36 PM
Points: 80, Visits: 395
hi team,

i tried this powershell script to run in sql agent job but i cannot get it to work. it says " PowerShell is: 'Access to the path 'D:\BackupLogs' is denied". the sql agent account is member of the admin group of my CMS server. i know i am missing something else should work like a charm. appreciate any help! thanks much!


Cheers!
[url=http://coffeeandsql.com/][/url]

Post #1432510
Posted Tuesday, April 30, 2013 8:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:12 PM
Points: 8, Visits: 273
I wanted to share a powershell script I whipped up that adds the results directly to a SQL table:
Basically, you'd setup your CMS, add all your servers, and then run a SQL job that would execute the Powershell script which would add the records right back into your repository's backup log table for central reporting purposes. Obviously though you could tweak this to only pull yesterday's backups, or only insert newer backups, etc..

In your repository DB, make a table and procedure:
CREATE TABLE [dbo].[BackupLog](
[BackupLogID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](50) NOT NULL,
[DBName] [varchar](50) NOT NULL,
[BackupTypeID] [varchar](5) NOT NULL,
[LastBackup] [datetime] NULL,
[DayInterval] [float] NULL,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BackupLog] ADD CONSTRAINT [DF_BackupLog_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate]
GO
CREATE PROCEDURE [dbo].[spBackupAdd]
(@ServerName varchar(50), @DBName varchar(50),
@BackupTypeID varchar(5), @LastBackup datetime, @DayInterval float)
AS

INSERT INTO BackupLog
(ServerName, DBName, BackupTypeID, LastBackup, DayInterval)
VALUES (@ServerName, @DBName, @BackupTypeID, @LastBackup, @DayInterval)
GO

Then make a powershell script, e.g. BackupLogger.ps1, and fill in your respective Server/Database:
#Server/DB for your repository
$RepServer = "<ServerName>"
$RepDatabase = "<DBName>"

## SQL connection function
# removes the need to install the SQL extensions for PowerShell
function Invoke-Sqlcmd2
{
param([string]$ServerInstance,
[string]$Database,
[string]$Query,
[Int32]$QueryTimeout=30
)
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]
}

## Begin Code
$con = "Server=$RepServer;Database=$RepDatabase;Integrated Security=True"
$cmd = "SELECT CASE WHEN PATINDEX('%,%', SRV.[server_name]) > 0 THEN
LEFT(SRV.[server_name], PATINDEX('%,%', SRV.[server_name])-1)
ELSE SRV.[server_name] END AS ServerName
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SRV
JOIN [msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP
ON SRV.server_group_id = GRP.server_group_id"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null

foreach ($srv in $dt)
{
$ServerName = $srv.ServerName
$Reply = Test-Connection -ComputerName $ServerName -Count 1 -Quiet
# if ping returns then continue
if ($Reply –eq “True”)
{
$con2 = "Server=$ServerName;Database=msdb;Integrated Security=True"
$cmd2 = "SELECT db.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases db
LEFT OUTER JOIN msdb..backupset b ON db.name = b.database_name
where b.type='D'
GROUP BY db.name, b.type
ORDER BY db.name, b.type"
$da2 = new-object System.Data.SqlClient.SqlDataAdapter ($cmd2, $con2)
$dt2 = new-object System.Data.DataTable
$da2.fill($dt2) | out-null

foreach ($backup in $dt2)
{
$BackupDB = $backup.name
$BackupDBType = $backup.type
$BackupDBLast = $backup.LastSuccessfulBackup
$BackupDBInt = $backup.IntervalInDays

$sql = "Exec spBackupAdd '$ServerName', '$BackupDB', '$BackupDBType', '$BackupDBLast', $BackupDBInt"
#Write-Host $sql
Invoke-Sqlcmd2 -serverinstance $RepServer -database $RepDatabase -query $sql
}
}
$Reply = ""
}

Then just create a SQL Agent job to run the script.
The case statement to strip out a comma I used when testing since my only server was the CMS server, so I had to register it as <ServerName>,port, e.g. MyServer,1433.

Bob
Post #1448291
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse