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 «««1234»»

Backup Monitoring and Reporting Expand / Collapse
Author
Message
Posted Thursday, October 8, 2009 5:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
Jackie,

I'm glad it worked out. My process for creating articles or blog posts start from something I've implemented at work. This approach requires I remove or account for customizations needed in my environment. Usually I catch these, but I missed this one.



Post #799876
Posted Thursday, October 22, 2009 6:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 9, 2010 4:10 AM
Points: 70, Visits: 117
Hi Chad
Sorry to bother you again on this, but I've added a new SQL instance recently and whilst it appears in the reports fine, for some reason the system databases (model, master, msdb) always show with success as False, even though the backups have worked. I note your comments in the article about the backup window, but these are backed up every 3 hours during the day so there should be some appearing within the backup window of 6pm to 6am.
On other instances, the system db's appear with success as True.
I don't remember having to set anything specifically up for this on the other instances but I get a deja vue feeling that I've missed something (again!)
It's not urgent anyway, as the prod db's are showing and that's the most important.
Thanks
Jackie
Post #807072
Posted Thursday, October 22, 2009 4:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
I don't see anything in the code where system databases are excluded. I would suggest running this query on the instances where the system databases are not reporting as successfully backed up:

SELECT @@servername, backup_set_id, database_name, backup_start_date, backup_finish_date, user_name,type
FROM backupset
WHERE type IN ('D','I')
AND backup_start_date > 2009-10-20
AND database_name in ('master','model','msdb')

One thought, I've seen servers where the @@servername does not return to the correct server name. This can cause problems for the report. This happpens if the server was renamed or master database restored from another server. You can fix this by using sp_dropserver and sp_addserver 'local', see Books Online.



Post #807493
Posted Tuesday, October 27, 2009 3:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 9, 2010 4:10 AM
Points: 70, Visits: 117
Hi Chad
It's a strange one and not sure why it only does it on that new instance. However, I've had to move the databases to one of the existing instances anyway and so the problem has "gone away". Thanks for your help again!
Cheers
Jackie
Post #809162
Posted Wednesday, December 2, 2009 8:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:02 PM
Points: 1,414, Visits: 4,545
working on deploying this now. working out some data integrity issues and i'm not using the built in reports. made my own. here is an example for any databases that haven't been backed up in the last 7 days.

select server_name as Server_Name, dbname as Database_Name, max(backup_finish_date) as Backup_End_Time, Backup_Type =
case type
when 'D' then 'Full backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
when 'F' then 'filegroup Backup'
when 'G' then 'Differential File backup'
else 'See Books Online'
end
from backupset_dim
where dbname not in ('master', 'msdb', 'ReportServer', 'ReportServerTempDB', 'pubs', 'Northwind', 'model')
and dbname not in (select dbname from db_exclude_dim)
and Type = 'D'
and backup_finish_date > '2009-01-01'
and server_name not in ('xxx', 'xxx4', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx')
group by dbname, server_name, Type
having max(backup_finish_date) < getdate() -7
order by server_name, database_name


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #827573
Posted Thursday, January 14, 2010 5:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 11:48 AM
Points: 9, Visits: 24
hello,

when I run the following powershell script, I get an error.

$destServer = 'VM-KMCSQL2005'
$destdb = 'BackupMonitoring'

#######################
function Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),
[string]$query=$(throw 'query is required.'))

Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
$da.fill($dt) > $null
$dt

} #Get-SqlData

#######################
function Get-BackupSet
{
param($srcServer,$backup_set_id)

$qry = @"
SELECT @@servername, backup_set_id, database_name, backup_start_date, backup_finish_date, user_name,type
FROM backupset
WHERE type IN ('D','I')
AND backup_start_date > '2008-01-01'
AND backup_set_id > $backup_set_id
"@
Get-SqlData $srcServer 'msdb' $qry

}# Get-BackupSet

#######################
function Write-DataTableToDatabase
{
param ($dataTable,$destTbl)

$connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "$destTbl"
$bulkCopy.WriteToServer($dataTable)

}# Write-DataTableToDatabase

#######################
## MAIN ##
#######################

Get-SqlData $destServer $destDb "SELECT * FROM server_dim" |
foreach {$dataTable = Get-BackupSet $_.server_name $_.max_backup_set_id; if ($dataTable) { Write-DataTableToDatabase $dataTable 'backupset_dim'} }


######################Error#######################
Exception calling "Fill" with "1" argument(s): "Incorrect syntax near '>'."
At H:\SQLServerBackupAnalysis\backupAnalysis.ps1:30 char:13
+ $da.fill <<<< ($dt)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
If someone can assist me, I will greatly appreciate it
Post #848032
Posted Thursday, January 14, 2010 8:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
Although I don't see anything wrong with this line. The error message pertains to it, so change this:

$da.fill($dt) > $null

to this

[void]$da.fill($dt)



Post #848061
Posted Tuesday, February 23, 2010 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 8:06 AM
Points: 218, Visits: 10,576
Hey Chad.. Great script! Thanks!! I ran into a slight problem. The Powershell script works great if all the SQL Servers are in the same or trusted domains. However, I've got one server that is in an untrusted domain, so the integrated security authentication that the Powershell script uses doesn't work. It would be awesome if this could be changed to also allow the use of SQL logins. I tried to add that, but failed, so I resorted to linking the stray server and getting that backup data via a stored procedure. (I also modified the server_dim table to include a domain column and changed the PS script to only look for servers in the same or trusted domains.)

Thanks again for the great script!!
Post #871160
Posted Tuesday, February 23, 2010 9:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 8:06 AM
Points: 218, Visits: 10,576
OK. I'm trying to create a SQL Agent job that runs this and it is failing. I think I'm missing something pretty basic.. Right now, the job only has one step - to run the Powerscript script. I'm using SQL 2008 and the step type in the job is set to PowerShell. I pasted the script into the Command window. When the job runs, it fails with a syntax error on line 25. Line 25 is basically the first line of code:

function Get-SqlData
{

Line 25 is the {.

Any suggestions? The script runs fine from a Powershell window.
Post #871206
Posted Wednesday, February 24, 2010 5:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
You shouldn't paste the Powershell script in the command windows. Instead for SQL 2008 setup a job with a Powershell command step and only have the path to the script plus and ampersand:
For example:

&C:\backupAnalysis.ps1

Modify the above with your path and ensure the service account has access to the path.




Post #871852
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse