Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

return missing-backups

Posted on 2014-11-25
3
Medium Priority
?
75 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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

916 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