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


Backup Monitoring and Reporting


Backup Monitoring and Reporting

Author
Message
cmille19
cmille19
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 724
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.



jackie.jones
jackie.jones
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 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
cmille19
cmille19
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 724
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.



jackie.jones
jackie.jones
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 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
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2790 Visits: 4674
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
Ejacksonjames
Ejacksonjames
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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: (Smile [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
If someone can assist me, I will greatly appreciate it
cmille19
cmille19
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 724
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)



rock on dude
rock on dude
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 10576
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!!
rock on dude
rock on dude
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 10576
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.
cmille19
cmille19
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 724
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.



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