SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


2008 Central Management Server - Schedule Multi Server Query?


2008 Central Management Server - Schedule Multi Server Query?

Author
Message
muhammadrazzaqpk-1032285
muhammadrazzaqpk-1032285
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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?
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1777 Visits: 1264
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.
Prema sambandam
Prema sambandam
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 258
this work worked like a charm. This is the one I am looking for. Thanks a lot.
best_yunus
best_yunus
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 122
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
Attachments
Current output.JPG (17 views, 15.00 KB)
Prema sambandam
Prema sambandam
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 258
Add @@servername in the query. It will give the server name in the output results.
Carlaabanes
Carlaabanes
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 474
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]
bobswi
bobswi
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 335
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
jswong05
jswong05
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 476
[b]
This sounds like exactly what I need to do. Can you post some code?


http://dbace.us/repriser/houstonsqlpassvideo.html
SQL Saturday #308

Jason
http://dbace.us
:-P
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search