November 2, 2011 at 8:12 am
Hi Guys
Would the below script be correct if I wanted to list all the Databases in an instance that is not in Simple Recovery, but only show the ones that don't have log backups?
select name from sys.databases where recovery_model_desc <> 'SIMPLE'
and name not in
(SELECT distinct database_name
FROM msdb.dbo.backupset
where type = 'L')
order by name
I've tested it and it seems fine.
Just wanted to be sure
thanks
November 2, 2011 at 8:35 am
If there is a job to purge bakup history, the query *might not* give you database names whose log backups have been taken.
November 2, 2011 at 8:38 am
viiki.seth (11/2/2011)
If there is a job to purge bakup history, the query *might not* give you database names whose log backups have been taken.
Would a maintenance plan that deletes old backup files count as Purging the backup history?
November 2, 2011 at 8:47 am
derekr 43208 (11/2/2011)
viiki.seth (11/2/2011)
If there is a job to purge bakup history, the query *might not* give you database names whose log backups have been taken.Would a maintenance plan that deletes old backup files count as Purging the backup history?
Only if you ask it too...
Select the first row order by id and see what date the backup was taken. That's how far you have history. If you know you were taking backups before that then you know something or someone is purging those tables.
The code to look for would look something like this =>
DECLARE @DeleteBeforeDate DATETIME
SET @DeleteBeforeDate = DATEADD(month,-1,GETDATE())
EXEC msdb..sp_delete_backuphistory @DeleteBeforeDate
November 2, 2011 at 8:53 am
Ninja's_RGR'us (11/2/2011)
derekr 43208 (11/2/2011)
viiki.seth (11/2/2011)
If there is a job to purge bakup history, the query *might not* give you database names whose log backups have been taken.Would a maintenance plan that deletes old backup files count as Purging the backup history?
Only if you ask it too...
Select the first row order by id and see what date the backup was taken. That's how far you have history. If you know you were taking backups before that then you know something or someone is purging those tables.
The code to look for would look something like this =>
DECLARE @DeleteBeforeDate DATETIMESET @DeleteBeforeDate = DATEADD(month,-1,GETDATE())
EXEC msdb..sp_delete_backuphistory @DeleteBeforeDate
Like this
SELECT top 1 database_name, backup_finish_date
FROM msdb.dbo.backupset
order by msdb.dbo.backupset.backup_set_id
?
November 2, 2011 at 8:59 am
Might be better like this =>
SELECT database_name, MIN(backup_finish_date) AS FirstBackup
FROM msdb.dbo.backupset
WHERE [type] = 'D' --Full backup
GROUP BY database_name
ORDER BY database_name
November 2, 2011 at 9:02 am
Ninja's_RGR'us (11/2/2011)
Might be better like this =>
SELECT database_name, MIN(backup_finish_date) AS FirstBackupFROM msdb.dbo.backupset
WHERE [type] = 'D' --Full backup
GROUP BY database_name
ORDER BY database_name
Cool
Is my original script okay though? - To check which DB's arent backing up logs??
November 2, 2011 at 9:04 am
Looks fine but that's untested on my end.
I would possibly use not exists because it's clearer to me and it can't fail if a name is null. I don't see how it could happen here, but you never know.
November 2, 2011 at 1:35 pm
Ninja's_RGR'us (11/2/2011)
Looks fine but that's untested on my end.I would possibly use not exists because it's clearer to me and it can't fail if a name is null. I don't see how it could happen here, but you never know.
Thanks
How would I change it using the not exists instead of not in?
Thanks
November 2, 2011 at 3:28 pm
November 3, 2011 at 12:06 am
Ninja's_RGR'us (11/2/2011)
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists
Thanks
November 3, 2011 at 12:26 am
Ninja's_RGR'us (11/2/2011)
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists
When using the not exists
Can my 1st select statement have a "where" clause in it before the "not exists"
I tried this
select name from sys.databases where recovery_model_desc <> 'SIMPLE'
and name not exists
(SELECT distinct database_name
FROM msdb.dbo.backupset
where type = 'L'
and server_name = @@SERVERNAME)
order by name
Doesnt seem to work - Incorrect syntax near the keyword 'exists'.
Thanks a lot
November 3, 2011 at 5:00 am
name goes in the correlation clause of the exists clause.
where whatever AND not exists (Select * FROM table where inner.col = outer.col)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply