June 18, 2013 at 5:52 pm
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!
June 19, 2013 at 1:22 am
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
June 19, 2013 at 4:01 am
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
June 19, 2013 at 5:44 am
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
---------------------------------------------------------------------
June 19, 2013 at 6:18 am
Thank you to everyone for posting! The help is most appreciated! I will work on this today and report back.
June 19, 2013 at 1:20 pm
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!
June 19, 2013 at 2:36 pm
glad to help, thanks for the feedback
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply