Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Detect missing db FULL backup and backup that db Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 5:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 7:52 AM
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!
Post #1464921
Posted Wednesday, June 19, 2013 1:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 25, Visits: 1,264
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


Post #1464972
Posted Wednesday, June 19, 2013 4:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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’! **
Post #1465038
Posted Wednesday, June 19, 2013 5:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 5,991, Visits: 12,923
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



---------------------------------------------------------------------

Post #1465098
Posted Wednesday, June 19, 2013 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 7:52 AM
Points: 4, Visits: 17
Thank you to everyone for posting! The help is most appreciated! I will work on this today and report back.
Post #1465121
Posted Wednesday, June 19, 2013 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 7:52 AM
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!
Post #1465352
Posted Wednesday, June 19, 2013 2:36 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 5,991, Visits: 12,923
glad to help, thanks for the feedback

---------------------------------------------------------------------

Post #1465386
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse