Solved

return missing-backups

Posted on 2014-11-25
3
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 51

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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

622 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