Need help on joins
-
i have ten databases in one server.
i wanted to get the data where database bakups are getdate()-1.If the database backup is not equal to getdate()-1 then i wanted to make it as null.
You may understand my requirement thru the below queries
I have used the below query but i am not getting the data as above
select * from master..sysdatabases where dbid>6
Output:
Ford
RAW
Maint
Common
hall
Joe
select distinct sd.name,bs.backup_finish_date from msdb..backupset bs left outer join master..sysdatabases sd on upper(bs.database_name)=upper(sd.name) where sd.dbid>6 and bs.backup_finish_date>getdate()-1
Output:
Common 2007-05-22 21:00:37.000
Ford 2007-05-22 21:01:23.000
Maint 2007-05-22 21:00:21.000
RAW 2007-05-22 21:00:35.000
But for the above i am not getting any data for joe database.
Required output is following.
name backup_finish_date
Common 2007-05-22 21:00:37.000
Ford 2007-05-22 21:01:23.000
Maint 2007-05-22 21:00:21.000
RAW 2007-05-22 21:00:35.000
joe NULL
Would any one help me on this?
Thanks,
Bagath
-
Heh... must be because SQL Server couldn't easily read your code, either.
You need the outer join to be on the database table, not the backup table... might want to throw a couple of NOLOCKS on this one, as well...
SELECT DISTINCT
sd.Name,
bs.Backup_Finish_Date
FROM MSDB..BackupSet bs
RIGHT OUTER JOIN Master..SysDataBases sd
ON bs.Database_Name = sd.Name
WHERE sd.DBID > 6
AND bs.Backup_Finish_Date > GETDATE()-1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, "AND bs.Backup_Finish_Date > GETDATE()-1" in WHERE clause changes RIGHT OUTER JOIN to INNER JOIN.
Probably what was originally requested supposed to look like this:
SELECT sd.Name, MAX(bs.Backup_Finish_Date) Last_Backup
FROM Master..SysDataBases sd
LEFT OUTER JOIN MSDB..BackupSet bs ON bs.Database_Name = sd.Name
WHERE sd.DBID > 6
GROUP BY sd.Name
HAVING (MAX(bs.Backup_Finish_Date) IS NULL OR MAX(bs.Backup_Finish_Date) 6 thing.
I don't think ignoring master db backup is a good idea.
_____________
Code for TallyGenerator -
Thanks to all....
Yes,where clause in jeff's query changes the join.
Here, i wanted to get the data having backups and no backups with in last day.
I have tried below as below....Let me know the your opinion on this....
SELECT sd.Name,bs.Backup_Finish_Date
FROM master..SysDataBases sd LEFT OUTER JOIN (SELECT bs.Backup_Finish_Date,bs.Database_Name FROM msdb..Backupset bs WHERE bs.Backup_Finish_Date>GETDATE()-1) as bs ON UPPER(sd.Name)=UPPER(bs.Database_Name) WHERE sd.DbId>6 ORDER BY 2 DESC
For the above if there is no backup with in last 24 hrs then it will show null.
Thanks,
Bagath
-
> Here, i wanted to get the data having backups and no backups with in last day.
You mean having backups but not within last day?
It's easy. Just remove IS NULL check:
SELECT sd.Name, MAX(bs.Backup_Finish_Date) Last_Backup
FROM Master..SysDataBases sd
INNER JOIN MSDB..BackupSet bs ON bs.Database_Name = sd.Name
WHERE sd.DBID > 6
GROUP BY sd.Name
HAVING MAX(bs.Backup_Finish_Date) < GETDATE()-1
_____________
Code for TallyGenerator -
I need both.
If having backup with in last 24 hrs then retrieve backup_finish_Date.
If no backup with ln last 24 hrs then retrieve null in the place of backup_finish_date.
So the condition must on two tables.
Did you get my criteria?
Thanks,
-
Yeah... didn't even look at that part, Serqiy... just knew the outer table was the wrong one and quit there... good catch.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
>> For the above if there is no backup with in last 24 hrs then it will show null.
Does that mean the query did want you wanted or not?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sergiy's solution will not show databases that have never been backup up which may be the reason why one is missing, this is my solution
SELECT DISTINCT sd.name,bs.backup_finish_date
FROM master.dbo.sysdatabases sd
LEFT JOIN msdb.dbo.backupset bs
ON bs.database_name = sd.name
AND bs.backup_finish_date > GETDATE()-1
WHERE sd.dbid > 6
Far away is close at hand in the images of elsewhere.
Anon. -
Jeff,
The query is not working.It is not showing null if there is no backup.
Thanks,
Bagath
-
Hey David,
Excellent solution.The Query is working fine with my criteria.
Let me say,Thank you very much to all of you for your support.
Once again thanks to all..................:-)
--Bagath.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply