0

I have a backup report which looks to see if all databases have been backed up within the last 30 days, and want to insert 'Not all databases backed up in last 30 days' into that field (datetime type) if it is the case that not all databases have been backed up within the last 30 days but i get the error Conversion failed when converting date and/or time from character string.

;WITH CTE AS
(
SELECT 
--sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb') AND bus.backup_finish_date > DATEADD(day,-30,GETDATE())
 AND sdb.Name NOT IN ('DBA')
AND sdb.state_desc  = 'ONLINE'
--GROUP BY sdb.Name
)
INSERT INTO Server.Database.Schema.TableName 
SELECT @@SERVERNAME  as serverName,
        --DatabaseName as DatabaseName,
        CASE WHEN LastBackupTime IS NULL THEN 'Not all databases backed up in last 30 days' ELSE MIN(LastBackUpTime) END as LastBackUpTime

FROM CTE
GROUP BY LastBackUpTime
1
  • 1
    @Jack, I assume/expect that the column LastBackupTime of the table you are trying to insert into has a datetime datatype? In that case you can't insert the string 'Not all databases backed up in last 30 days', this would need a (var)char datatype. Jun 13, 2019 at 9:15

1 Answer 1

1

The below should return 'Not all databases backed up' if any of the databases haven't been backed up, and 'All databases backed up as of DD/MM/YYYY' if they have (this will be the highest backup date found)

;WITH CTE AS
(
SELECT 
--sdb.Name AS DatabaseName,
ISNULL(MAX(backup_finish_date),0) AS LastBackUpTime,
[sdb].[name] AS DatabaseName
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE (bus.type = 'D' OR bus.[database_name] IS NULL)
AND sdb.name NOT IN ('master', 'model', 'msdb', 'tempdb','DBA')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT 
    CASE
        WHEN MIN([CTE].[LastBackUpTime]) IS NULL THEN 'Not all databases backed up'
        WHEN MIN([CTE].[LastBackUpTime]) <= DATEADD(dd,-30,GETDATE()) THEN 'Not all databases backed up'
        ELSE 'All databases backed up as of ' + CAST(MAX([CTE].[LastBackUpTime]) AS NVARCHAR(100))
    END
FROM CTE

The script looks for the highest backup date for all non-system (and non-DBA) databases, and returns the datetime equivalent of 0 if there are none (this converts to 1900-01-01 00:00:00.000). We can then check whether the minimum date is in our 30 day region or not.

The actual issue you were experiencing was because your CASE statement returned both a DATETIME data type and an VARCHAR data type - because of data type precedence for a CASE statement SQL server will always choose the data type of the highest precedence as the data type of that field - in this case that was a DATETIME.

To prevent this we simply need to CAST the DATETIME field as a VARCHAR, which prevents the implicit conversion from VARCHAR to DATETIME (lower to higher precedence)

9
  • Amazing thank you. This works, except that for servers on which the specified databases have never been backed up I get NULL rather than a 'not backed up' message. I want to add something like ' WHEN LastBackUpTime IS NULL THEN 'Databases never backed up' but when i run this (on CMS, against 55 servers) the 4 which have never been backed up are simply left off the list.
    – jack
    Jun 13, 2019 at 10:41
  • @jack I've added an additional line to the CASE statement that should fix that issue Jun 13, 2019 at 10:42
  • Thank you! Only problem is trying to insert into a column, back to Conversion failed when converting date and/or time from character string (I run this as an insert into a table on multiple servers and then use the table to make a report).
    – jack
    Jun 13, 2019 at 10:55
  • @jack you didn't provide the DDL (CREATE TABLE) in your question - what data type is the field being inserted into? Jun 13, 2019 at 10:56
  • LastyBackUpTime datetime - i changed this to nvarchar but still get the same problem. I can make the DDL whatever it has to be to get it to work.
    – jack
    Jun 13, 2019 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.