Backup Monitoring and Reporting

Question

Post reply

Backup Monitoring and Reporting

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

navendu.mohanty Valued Member Points: 66 More actions · Answer 1

Somebody please help me . i can't run backupAnalysis.ps1 script

I dont have powershell on my server since its a Windows 2003 Server

Is there alternateive procedures written for this.

Kindly help

Thanks

Navendu

navendu.mohanty Valued Member Points: 66 More actions · Answer 2

Somebody please help me . i can't run backupAnalysis.ps1 script

I dont have powershell on my server since its a Windows 2003 Server

Is there alternateive procedures written for this.

Kindly help

Thanks

Navendu

navendu.mohanty Valued Member Points: 66 More actions · Answer 3

Can you please help me . i can't run backupAnalysis.ps1 script

I dont have powershell on my server since its a Windows 2003 Server

Is there alternateive procedures written for this.

Kindly help

Thanks

Navendu

jackie.jones SSC Eights! Points: 810 More actions · Answer 4

Hello Navendu

They're probably still sleeping over in the USA so thought I would post the link where you can download powershell for windows server 2003. My server is Windows 2003 and powershell worked fine. Once you've downloaded it, select it from the start menu. It loads a window similar to a command prompt. Navigate to where you've stored your backupanalysis.ps1 file and then type .\backupanalysis.ps1 (exactly as Chad detailed in his instructions)

The link is http://www.microsoft.com/downloads/details.aspx?FamilyId=10EE29AF-7C3A-4057-8367-C9C1DAB6E2BF&displaylang=en

Regards

Jackie

jackie.jones SSC Eights! Points: 810 More actions · Answer 5

Hello Chad

That's great - hadn't spotted that extra update was required and all is fine now - you're a star!

I did give myself a panic moment though by gleefully removing all data and then running the jobs to re-populate them and then wondering why there was nothing there. Your instructions saved the day again though as I noticed the comment that the powershell script is set to only pick up new rows. Temporarily removed that line, ran it all again and Bingo

Thanks again

Jackie

cmille19 SSCertifiable Points: 5970 More actions · Answer 6

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 SSC Eights! Points: 810 More actions · Answer 7

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 SSCertifiable Points: 5970 More actions · Answer 8

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 SSC Eights! Points: 810 More actions · Answer 9

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 SSC-Dedicated Points: 30014 More actions · Answer 10

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 SSC Journeyman Points: 85 More actions · Answer 11

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

cmille19 SSCertifiable Points: 5970 More actions · Answer 12

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 Ten Centuries Points: 1236 More actions · Answer 13

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 Ten Centuries Points: 1236 More actions · Answer 14

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 SSCertifiable Points: 5970 More actions · Answer 15

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.