Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detect missing db FULL backup and backup that db


Detect missing db FULL backup and backup that db

Author
Message
sandtoad2
sandtoad2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
I have a problem I am trying to solve. I would like to run a query of some sort that will compare a list of active databases on an instance of SQL and compare it to the backup history. If a full backup exists for that db, then ignore it. However, if a FULL backup does NOT exist for that db, then perform a FULL backup of that db.

So, in simpler form:

If the database has a FULL backup, do nothing
If the database does NOT have a FULL backup, take a FULL backup of that db.

Any help would be appreciated. I can find several excellent scripts here that will check to see if a db has a backup...but nothing that will allow me to compare and then perform a backup. I'm not sure how to put all of it together.

Thanks!
r.dragoi-1095738
r.dragoi-1095738
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 2403
Hi,
Take a look at this query , it might help !


SELECT TOP (10) *
FROM
msdb.dbo.backupmediafamily AS fm
INNER JOIN msdb.dbo.backupmediaset AS ms ON fm.media_set_id = ms.media_set_id
INNER JOIN msdb.dbo.backupfile AS bf
INNER JOIN msdb.dbo.backupset AS bcs ON bf.backup_set_id = bcs.backup_set_id ON ms.media_set_id = bcs.media_set_id
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
This query gives you the date of the last FULL backup of each database or gives you the commandline to perform a FULL backup. Adjust this commandline in the query to fit your needs. You could loop through this output to execute the generated comand lines.
select 
db.name
, bs.type
, bs.backup_finish_date
, case when bs.type is null
then 'BACKUP DATABASE [' + db.name + '] TO DISK = N''C:\SQL\Backup\' + db.name + '.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + db.name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
else null
end as Backup_command
from
( -- use a sub-select to determine the date of the last FULL backup for each database
select database_name
, type
, MAX(backup_finish_date) as backup_finish_date
from msdb..backupset
where type = 'D'
group by
database_name
, type) bs
right outer join master.sys.databases db
on bs.database_name = db.name
where db.name <> 'tempdb' -- it's not possible to backup the TEMPDB
order by bs.backup_finish_date desc



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
I just cribbed this from a job I run so amend to your needs, but it backups any databases with no backups

create PROCEDURE [dbo].[usp_FirsttimeBkUp] 

AS

set nocount on

declare @dbase_name sysname,
@dbase_dbid int,
@SQLString nvarchar(1000),
@backupPath nvarchar (250)

declare DBASE cursor for

-- identify databases that have never been backed up

select a.[name], a.[database_id]
from master.sys.databases a
left join msdb.dbo.backupset b
on a.[name] = b.database_name
and b.type='D'
where a.[name] not in ('model','tempdb')
and b.database_name is null
and a.state_desc = 'online'



open DBASE

fetch next from DBASE
into @dbase_name,@dbase_dbid

IF @@fetch_status <> 0
print 'no databases need to be backed up'
ELSE
print 'the following databases will be backed up ...'

while @@fetch_status = 0
begin


-- set the backup path
SET @backupPath =

'your backup path\'
+ @dbase_name + '.bak'

SET @SQLString = 'BACKUP DATABASE [' + @dbase_name + '] TO DISK = N''' + @backupPath + ''''
SET @SQLString = @SQLString + ' WITH INIT, STATS = 10'

-- backup the database
print ' *** '+@dbase_name+' ***'
EXEC sp_executesql @SQLString

fetch next from DBASE
into @dbase_name,@dbase_dbid
end

close DBASE
deallocate DBASE


GO



---------------------------------------------------------------------
sandtoad2
sandtoad2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
Thank you to everyone for posting! The help is most appreciated! I will work on this today and report back.
sandtoad2
sandtoad2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
Just to follow up....

SSCertifiable...thank you for the help! This is exactly what was needed. I made some path changes, built the procedure and then executed it. It found newly created databases that did not have a full backup yet and took a full backup of them so the log backups would not fail.

Thanks again to everyone for the help!
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
glad to help, thanks for the feedback

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search