SQLServerCentral / Discuss Content Posted by Chad Miller / Article Discussions / Article Discussions by Author / Backup Monitoring and Reporting / Latest Posts

RE: Backup Monitoring and Reporting

Suresh Channamraju — Fri, 16 Jul 2010 13:00:23 GMT

Sorry.. please ignore..

RE: Backup Monitoring and Reporting

cmille19 — Wed, 24 Feb 2010 05:51:19 GMT

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.ps1Modify the above with your path and ensure the service account has access to the path.

RE: Backup Monitoring and Reporting

rock on dude — Tue, 23 Feb 2010 09:01:57 GMT

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.

RE: Backup Monitoring and Reporting

rock on dude — Tue, 23 Feb 2010 08:15:20 GMT

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!!

RE: Backup Monitoring and Reporting

cmille19 — Thu, 14 Jan 2010 20:24:27 GMT

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)

RE: Backup Monitoring and Reporting

Ejacksonjames — Thu, 14 Jan 2010 17:00:41 GMT

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,typeFROM backupsetWHERE 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 : DotNetMethodExceptionIf someone can assist me, I will greatly appreciate it

RE: Backup Monitoring and Reporting

alen teplitsky — Wed, 02 Dec 2009 08:51:27 GMT

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_dimwhere 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, Typehaving max(backup_finish_date) < getdate() -7order by server_name, database_name

RE: Backup Monitoring and Reporting

jackie.jones — Tue, 27 Oct 2009 03:40:26 GMT

Hi ChadIt'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!CheersJackie

RE: Backup Monitoring and Reporting

cmille19 — Thu, 22 Oct 2009 16:07:04 GMT

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,typeFROM backupsetWHERE type IN ('D','I')AND backup_start_date > 2009-10-20AND 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.

RE: Backup Monitoring and Reporting

jackie.jones — Thu, 22 Oct 2009 06:15:52 GMT

Hi ChadSorry 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

RE: Backup Monitoring and Reporting

cmille19 — Thu, 08 Oct 2009 05:18:16 GMT

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.

RE: Backup Monitoring and Reporting

jackie.jones — Thu, 08 Oct 2009 03:09:24 GMT

Hello ChadThat'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 BingoThanks againJackie

RE: Backup Monitoring and Reporting

jackie.jones — Thu, 08 Oct 2009 02:33:45 GMT

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=enRegardsJackie

RE: Backup Monitoring and Reporting

navendu.mohanty — Thu, 08 Oct 2009 01:57:10 GMT

Can you please help me . i can't run backupAnalysis.ps1 scriptI dont have powershell on my server since its a Windows 2003 ServerIs there alternateive procedures written for this.Kindly help ThanksNavendu

RE: Backup Monitoring and Reporting

navendu.mohanty — Thu, 08 Oct 2009 01:56:23 GMT

Somebody please help me . i can't run backupAnalysis.ps1 scriptI dont have powershell on my server since its a Windows 2003 ServerIs there alternateive procedures written for this.Kindly help ThanksNavendu

RE: Backup Monitoring and Reporting

navendu.mohanty — Thu, 08 Oct 2009 01:55:34 GMT

RE: Backup Monitoring and Reporting

navendu.mohanty — Thu, 08 Oct 2009 01:54:11 GMT

Somebody please help me .I dont have powershell on my server since its a Windows 2003 ServerIs there alternateive procedures written for this.Kindly help ThanksNavendu

RE: Backup Monitoring and Reporting

cmille19 — Wed, 07 Oct 2009 15:58:51 GMT

[quote][b]jackie.jones (10/7/2009)[/b][hr]Hello I think I did that bit right because when I looked in the table dt_dim where it had previously had Nulls for the weekends, it now has the dates correctly shown up until the end of this year, with no gaps for weekends.In the reports however, it still only shows the week days in the drop down. When I checked the query in the reports the problem seemed to be due to the link between dt_dim and backup_fact (by the dt_id) as the backup_fact contains gaps for the weekends presumably. E.G. the dt_id jumps from 40079 to 40082 and from 40086 to 40089 etc, etc), so I guess that's why it doesn't show in the drop down? I guessed therefore that I've missed something else to update.Thanks for your speedy reply tooJackie[/quote]Looking at this little further, the insert_backup_fact_sp is also customized to my environment and checks dw (day of week) between 2 and 6 (Monday and Saturday). Remove/comment out this line:AND d.dw BETWEEN 2 AND 6Then truncate backup_fact and re-run inserver_backup_fact and update_backup_fact_sp

RE: Backup Monitoring and Reporting

jackie.jones — Wed, 07 Oct 2009 02:04:44 GMT

Hello I think I did that bit right because when I looked in the table dt_dim where it had previously had Nulls for the weekends, it now has the dates correctly shown up until the end of this year, with no gaps for weekends.In the reports however, it still only shows the week days in the drop down. When I checked the query in the reports the problem seemed to be due to the link between dt_dim and backup_fact (by the dt_id) as the backup_fact contains gaps for the weekends presumably. E.G. the dt_id jumps from 40079 to 40082 and from 40086 to 40089 etc, etc), so I guess that's why it doesn't show in the drop down? I guessed therefore that I've missed something else to update.Thanks for your speedy reply tooJackie

RE: Backup Monitoring and Reporting

cmille19 — Tue, 06 Oct 2009 15:20:41 GMT

[quote][b]jackie.jones (10/6/2009)[/b][hr]Hello ChadI've tried to change it so that it picks up the backups over the weekend but I've failed miserably!Could you advise what I need to do to modify this so that the report shows every day in the drop down, rather than just Monday-Friday. Thanks very much in advance[/quote]You'll need to change the insert dt_dim logic. This is where the query takes into account your backup windows. I'm using Monday through Thursday midnight through the 6 AM the next day 30 hours and on Friday a 78 hours. These lines need to be changed:WHEN DATEPART(dw,SeqDate) BETWEEN 2 AND 5 THEN DATEADD(hh,30,SeqDate)WHEN DATEPART(dw,SeqDate) = 6 THEN DATEADD(hh,78,SeqDate)2 through 5 is Monday through Thursday6 is SaturdayDoes this make sense? If you want, post your backup window and schedule and I'll tweak the query and post it here.

RE: Backup Monitoring and Reporting

jackie.jones — Tue, 06 Oct 2009 01:59:04 GMT

Hello ChadI've tried to change it so that it picks up the backups over the weekend but I've failed miserably!Could you advise what I need to do to modify this so that the report shows every day in the drop down, rather than just Monday-Friday. Thanks very much in advance

RE: Backup Monitoring and Reporting

BJCharly — Tue, 08 Sep 2009 10:37:09 GMT

Nice article, thanks

RE: Backup Monitoring and Reporting

jackie.jones — Fri, 04 Sep 2009 02:53:56 GMT

Really really helpful and well written - thank you very much.

RE: Backup Monitoring and Reporting

cmille19 — Fri, 05 Jun 2009 05:28:00 GMT

[quote][b]Rui Nogueira (6/4/2009)[/b][hr]Good article, Backup Monitoring and ReportingThere is a mistake in it: msdb.baclkupset -> msdb.backupsetThank You[/quote]Thanks -- I made a few spelling and grammar corrections, it might take a few days to show up.

RE: Backup Monitoring and Reporting

cmille19 — Fri, 05 Jun 2009 05:17:04 GMT

[quote][b]sanderstad (6/5/2009)[/b][hr]When I get execute the jobs I get the following error by starting the powershell script:[code]Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable)Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable). Process Exit Code 0. The step succeeded.[/code][/quote]Try running update_server_dim_sp this wil update the server_dim.max_backupset_id column. Also keep in mind the primary key is defined as server_name and backup_set_id. The server name value is obtained from @@servername, so its possible to have duplicate names of your SQL Servers in your environment. Check @@servername on all servers you are polling and verify there are no duplicates.

RE: Backup Monitoring and Reporting

sanderstad — Fri, 05 Jun 2009 01:48:35 GMT

When I get execute the jobs I get the following error by starting the powershell script:[code]Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable)Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable). Process Exit Code 0. The step succeeded.[/code]

RE: Backup Monitoring and Reporting

Dugi — Fri, 05 Jun 2009 01:03:36 GMT

Yea, very nice article! It is in my briefcase now! Thank you for sharing! ;-)

RE: Backup Monitoring and Reporting

Rui Nogueira — Thu, 04 Jun 2009 10:33:41 GMT

Good article, Backup Monitoring and ReportingThere is a mistake in it: msdb.baclkupset -> msdb.backupsetThank You

RE: Backup Monitoring and Reporting

Paul Randal — Thu, 04 Jun 2009 10:19:30 GMT

Chad - excellent. I'll be adding a link to this in all my DBA classes.

RE: Backup Monitoring and Reporting

Ted Krueger — Thu, 04 Jun 2009 08:09:57 GMT

Very nice! I would optimize some things in the coding for large installations with hundreds of backups going on every hour (logs etc..) though. Example would be to the exclusion table and the "AND d.name NOT IN (SELECT db_name FROM dbautility.dbo.db_exclude)". Could be replaced with a JOIN logic and get the NOT out of there.All in all I enjoyed reading it and I can see this benefiting quite a few DBAs out there. Thanks!

Backup Monitoring and Reporting

cmille19 — Wed, 03 Jun 2009 11:33:24 GMT

Comments posted to this topic are about the item [B]Backup Monitoring and Reporting[/B]