January 15, 2016 at 6:19 am
Hello everyone,
I am having a problem with my script. The problem is when add an additional field to my script the query results report 837 rows instead of 31. I hit a brick wall and I am not sure how to resolve the problem.
When I run the following script I get the correct number of rows 31.
select * from
(SELECT ServerName, DatabaseName, DbId, error, MessageText, [datetime] FROM [dbo].[CheckDBHist]
WHERE datetime < (SELECT getdate()) and
datetime > (SELECT getdate()-1) and
ServerName = 'NameOfServer') allnames
inner join
(SELECT distinct servername FROM [dbo].[DBBackupFileDate]
WHERE BackupStartDate < (SELECT getdate()) and
BackupStartDate > (SELECT getdate()-1) and
ServerName = 'NameOfServer') uniquenames
on uniquenames.ServerName = allnames.ServerName
order by datetime desc
However, when I add the extra field and run the script like this, I get 837 rows.
select * from
(SELECT ServerName, DatabaseName, DbId, error, MessageText, [datetime] FROM [dbo].[CheckDBHist]
WHERE datetime < (SELECT getdate()) and
datetime > (SELECT getdate()-1) and
ServerName = 'NameOfServer') allnames
inner join
(SELECT distinct servername, BackupStartDate FROM [dbo].[DBBackupFileDate]
WHERE BackupStartDate < (SELECT getdate()) and
BackupStartDate > (SELECT getdate()-1) and
ServerName = 'NameOfServer') uniquenames
on uniquenames.ServerName = allnames.ServerName
order by datetime desc
I am not sure if I am dealing with a join issue, your help will be greatly appreciated.
Thank you
January 15, 2016 at 6:33 am
Run that second subquery on its own, and you'll see it returns a different result for each of the two guises. That's why the whole thing returns a different number of rows. If you remove the DISTINCT from the subquery, both will return the same number of rows.
John
January 15, 2016 at 6:53 am
I am not sure if I understood you correctly, I removed the DISTINCT clause and ran the script. When I ran the script like this I get 31 rows, which is correct.
SELECT servername, BackupStartDate FROM [dbo].[DBBackupFileDate]
WHERE BackupStartDate < (SELECT getdate()) and
BackupStartDate > (SELECT getdate()-1) and
ServerName = 'NameOfServer'
However, I want to join the two tables to report in the following manner.
ServerName DatabaseName DbId error MessageText datetime servername BackupStartDate
When I run the script like this, I now get 961 rows.
select * from
(SELECT ServerName, DatabaseName, DbId, error, MessageText, [datetime] FROM [dbo].[CheckDBHist]
WHERE datetime < (SELECT getdate()) and
datetime > (SELECT getdate()-1) and
ServerName = 'NameOfServer') allnames
inner join
(SELECT servername, BackupStartDate FROM [dbo].[DBBackupFileDate]
WHERE BackupStartDate < (SELECT getdate()) and
BackupStartDate > (SELECT getdate()-1) and
ServerName = 'NameOfServer') uniquenames
on uniquenames.ServerName = allnames.ServerName
January 15, 2016 at 7:11 am
So you have a table of DBCC check results and a table of backup results, and you want to join them? I'm not sure why you'd want to do that. At the moment, for each server, you're matching up every backup with every DBCC check, which I'm sure isn't your intention. Wouldn't it be better to keep apples and oranges separate, and have a different report for DBCC checks and backups?
John
January 15, 2016 at 7:51 am
How about doing something like this:
SELECT ch.ServerName,
ch.DatabaseName,
ch.DbId,
ch.error,
ch.MessageText,
ch.[datetime] ,
MAX(bk.BackupStartDate) AS BackupStartDate
FROM [dbo].[CheckDBHist] ch
JOIN [dbo].[DBBackupFileDate] bk ON ch.ServerName = bk.ServerName
WHERE ch.datetime < (SELECT getdate())
AND ch.datetime > (SELECT getdate()-1)
AND ch.ServerName = 'NameOfServer'
AND bk.BackupStartDate < (SELECT getdate())
AND bk.BackupStartDate > (SELECT getdate()-1)
GROUP BY ch.ServerName,
ch.DatabaseName,
ch.DbId,
ch.error,
ch.MessageText,
ch.[datetime];
January 15, 2016 at 7:53 am
I should explain myself a little better, I have 4 SQL jobs which do the following.
1. Check DB integrity and backup database on server.
2. Copy SQL BAK files from server to NAS. (In case server crashes the BAK files are safe)
3. Test database restore by copying BAK files from NAS to remote server.
4. Report database restore test.
The goal is to make sure that the BAK files are being copied to the NAS and to test the integrity of the SQL BAK files.
I have steps 1,2,3 working perfectly, I am stuck on number 4. For step 4, I would like to report to join the [dbo].[CheckDBHist] and [dbo].[DBBackupFileDate] to show me the restore and backup date for each database for a specific server. This way I can verify that step 3 is working correctly to make sure that it did copy last nights backups from the NAS.
January 15, 2016 at 8:01 am
Does Luis's solution get you anywhere close? If not, you might try including database name (or id) and date (with time portion, if any, stripped out) in your join predicate.
John
January 15, 2016 at 8:52 am
Luis, Thank You!! 😀
January 15, 2016 at 9:17 am
RayMan007 (1/15/2016)
Luis, Thank You!! 😀
Make sure that you don't need an additional condition on your join to get a backup date for each database instead of one per server.
You might want to use a full outer join as well to include all databases.
Something like this:
SELECT ch.ServerName,
ch.DatabaseName,
ch.DbId,
ch.error,
ch.MessageText,
ch.[datetime] ,
MAX(bk.BackupStartDate) AS BackupStartDate
FROM [dbo].[CheckDBHist] ch
JOIN [dbo].[DBBackupFileDate] bk ON ch.ServerName = bk.ServerName AND ch.DbId= bk.DbId
WHERE ch.datetime < (SELECT getdate())
AND ch.datetime > (SELECT getdate()-1)
AND (ch.ServerName = 'NameOfServer' OR bk.ServerName = 'NameOfServer')
AND bk.BackupStartDate < (SELECT getdate())
AND bk.BackupStartDate > (SELECT getdate()-1)
GROUP BY ch.ServerName,
ch.DatabaseName,
ch.DbId,
ch.error,
ch.MessageText,
ch.[datetime];
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply