Solved

return missing-backups

Posted on 2014-11-25
3
70 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 48

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax Issue with SSIS module 26 104
Select values in a row based on values in another row in sql 4 26
MYSQL responding very slow 3 24
IIF in access query 19 25
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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