Solved

return missing-backups

Posted on 2014-11-25
3
67 Views
Last Modified: 2014-11-25
can you suggest if there is more efficient (shorter) code or better logic to do this in sQL 2012?

http://www.mssqltips.com/tipImages2/2551_checkmissingbackups.txt
0
Comment
Question by:25112
3 Comments
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40464833
Yes. Missing_Backups should be a temporary table (#Missing_Backups),
Also, I would avoid the UNION:
insert into #Missing_Backups  -- Assuming you are going to use temporary table
SELECT d.name AS Database, CASE b.Type
                      WHEN 'D' THEN 'Full' 
                       ELSE 'Trn'
                      END as Type,
       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS [Last Backup]
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
           GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1) 
  AND d.name <> 'tempdb'

Open in new window


The rest of the code is to transform in HTML to send the information by email. Depends on what you want to do with the information.
Also, the example considers missing backup as a database that don't has a backup from more than 1 day.
0
 
LVL 5

Author Comment

by:25112
ID: 40465239
Thank you.

Jim, I will remember that. the goal here was to make sure to use the latest syntax/provision, as our msdb files are big and planning to deploy in big servers with many databases.

thank you again.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now