March 28, 2008 at 12:17 pm
I have a transaction log backup maintenance plan that runs every 15 minutes to back up the logs for all databases, but the task fails if someone adds a new database to the server and doesn’t make a full backup before the maintenance plan runs.
Does someone know of a good way to detect which databases lack full backups and have the full backups made before the transaction log backup script runs?
Another way to approach this would be to not rely on a single "backup all transaction logs" step, but to be more specific about what gets backed up, and have a supplemental step that reports on databases that are not part of any backup maintenance plan.
Thank you,
Mike Chabot
March 28, 2008 at 1:16 pm
What version are you working with? Enterprise? Standard? Compact? XE? 2005, right?
IF there were no SSMS tool available, I would suggest that you have to have a quasi RBAR process where you cycle through the database names, run the backup for that database individually, and then write a record to an auditing table that the backup was executed. That way, you could (a) control for failure on an individual database and record it, and (b) have another table that you could use to compare against the system table of databases to identify which databases were not implemented correctly.
Even better would be to have the appropriate controls in place that databases and/or tables are not added willy-nilly into the environment, but there has to be a formal review. Making offenders clean up after themselves (if they don't get themselves fired) will often fix misbehaving developers.
There are plenty other threads on the forums that address this, if I remember correctly. Have you searched?
March 28, 2008 at 2:13 pm
It is SQL Server 2005 Standard sp2.
I found the code that detects whether a backup has ever been made, which is the core of what I'm trying to do.
SELECT DB_NAME(rs.database_id)
FROM sys.database_recovery_status rs
WHERE rs.last_log_backup_lsn IS NULL
I just have to build in the other logic that surrounds this. I'm hoping to use as much built-in functionality as I can since those solutions are generally more maintainable in the long run.
March 28, 2008 at 5:40 pm
This is what I have now
--Use trailing slash
DECLARE @PathToBackupFolder varchar(100)
SET @PathToBackupFolder = 'D:\DatabaseBackups\'
DECLARE @PathToSpecificBackupFolder varchar(100)
DECLARE @FileName varchar(100)
/*IF object_id('tempdb..#DatabaseList') IS NOT NULL
BEGIN
DROP TABLE #DatabaseList
END*/
CREATE TABLE #DatabaseList (
id int,
RecoveryMode sql_variant,
OnlineStatus sql_variant
)
SET NOCOUNT ON
EXEC master.dbo.sp_msforeachdb
"USE [?]
INSERT INTO #DatabaseList
SELECT db_id(),DATABASEPROPERTYEX('?', 'Recovery'),DATABASEPROPERTYEX('?', 'Status')
"
/*SELECT *
FROM #DatabaseList
*/
DECLARE @dbname varchar(36)
DECLARE @cmd varchar(255)
DECLARE dbname_cursor CURSOR LOCAL FAST_FORWARD FOR
--We only care about databases that are online and that have a recovery mode = FULL
SELECT db_name(database_id)
FROM #DatabaseList dl
INNER JOIN sys.database_recovery_status rs
ON (
rs.last_log_backup_lsn IS NULL
AND dl.RecoveryMode = 'FULL'
AND dl.OnlineStatus = 'ONLINE'
AND rs.database_id = dl.id
)
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @dbname
WHILE @@fetch_status = 0
BEGIN
--Make sure the backup folder exists
SET @PathToSpecificBackupFolder = @PathToBackupFolder + @dbname + '\'
EXECUTE master.dbo.xp_create_subdir @PathToSpecificBackupFolder
SET @filename = @dbname + '_backup_' + convert(char(8),getdate(),112) + left(replace(convert(char(8),getdate(),8),':',''),4)
SELECT @cmd ='BACKUP DATABASE ' + @dbname + ' TO DISK=''' + @PathToSpecificBackupFolder + @filename +'.bak'' WITH INIT, NAME=''' + @filename + ''''
PRINT @cmd
EXECUTE (@cmd)
FETCH NEXT FROM dbname_cursor INTO @dbname
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor
March 29, 2008 at 3:54 pm
Script will result list of database(s) with no full backup:
select name from master..sysdatabases
where name not in(select distinct database_name from msdb..backupset where type='D')
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 29, 2008 at 4:40 pm
Michael Zilberstein wrote a nice blog post that describes why that method doesn't work.
I tested querying the database_recovery_status table and that table does seem to supply reliable information.
March 29, 2008 at 8:19 pm
M Chabot (3/28/2008)
...but the task fails if someone adds a new database to the server and doesn’t make a full backup before the maintenance plan runs...
Hi Mike...
Not the answer that you were probably looking for...
The quote above would be the first thing I'd fix... no one but the DBA's should have the type of access to create a database and no one but the DBA's and certified operators should have the ability to do an adhoc backup. In all cases, there should be a "procedure" that is followed because even a good DBA can forget to do something if there's no checklist.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply