December 8, 2011 at 8:48 am
muhammadrazzaqpk-1032285 (12/8/2011)
are you guys talking about me being a spamer? I have subscribed for this topic and seeking for help.This is first time, I have actually replied to any topic, usually I find by just reading.
Please clarify which user you talking about?
no we are talking about a user called "1260221107", there where a number of topics which this user had put an advertising message for Gucci and other products.
these where reported to the admins who removes them from the topics if they are found to be spam.
as this has happened you where the last offical none spamer to post which may make it seem like we are talking about you.
you can see from the white quote boxes in mine and Dev's posts the chain working from the inside out.
December 8, 2011 at 8:56 am
Thanks for the clarification. Appreciate it -
December 8, 2011 at 9:08 am
Try moving the open quote onto the same line as the invoke-sqlcmd -query. I think it has to do with white space and how it is reading the script. I'll take a look at it when I get into the office.
December 8, 2011 at 11:36 am
Thanks - I have given it every try - As you know I am not good at all with powershell scripting but it will be helpful for me if a working script where I have to just change name of my server and query.
Perhaps if you take my script and I am sure you will be able to reproduce the error.
I really do appreciate your time.
December 8, 2011 at 1:10 pm
muhammadrazzaqpk-1032285 (12/8/2011)
Thanks - I have given it every try - As you know I am not good at all with powershell scripting but it will be helpful for me if a working script where I have to just change name of my server and query.Perhaps if you take my script and I am sure you will be able to reproduce the error.
I really do appreciate your time.
Hi
Run this script. It should create a SQL Agent job that when run will run your query across all SQL Servers that are registered in your CMS. The results will be saved to a text file on your E drive which you can load into a database using SSIS or some other method.
USE [msdb]
GO
/****** Object: Job [Last Successful Backup] Script Date: 12/8/2011 1:02:48 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/8/2011 1:02:48 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Last Successful Backup',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [PowerShell] Script Date: 12/8/2011 1:02:48 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PowerShell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'$instanceNameList = invoke-Sqlcmd -query "
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
" -serverinstance "myserver\dba"
$results = @()
foreach($instanceName in $instanceNameList)
{$results += Invoke-Sqlcmd -Query "
SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
where b.type=''D''
GROUP BY a.name, b.type
ORDER BY a.name, b.type
" -ServerInstance $instanceName.Name}
$results| Where-Object {$_} | Export-Csv E:\Backup_log -NoTypeInformation',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
December 9, 2011 at 9:18 am
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?
December 9, 2011 at 9:32 am
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.
April 27, 2012 at 9:13 am
this work worked like a charm. This is the one I am looking for. Thanks a lot.
December 29, 2012 at 11:13 pm
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
January 2, 2013 at 9:45 am
Add @@servername in the query. It will give the server name in the output results.
March 19, 2013 at 1:50 am
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!
April 30, 2013 at 8:27 pm
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
MCSA SQL 2014
October 20, 2014 at 1:12 pm
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
😛
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply