November 23, 2010 at 8:09 am
Hi all.
I'm about to deploy the Hallengren (ola.hallengren.com) script for maintenance solution. There is one requirement missing that we have (as I can see) and that is if there are some databases that has been created since last translogbackup, then run a full backup, and then run the log backup afterwards.
Has anyone of you done this before? We have a 8TB sharepoint environment with about 400 server globally and need to automate a bit more than we have (mostly weekly full, 6days diff, hourly log).
Any help appreciated.
_R
November 23, 2010 at 8:28 am
I think it would be good practice to always do an initial full backup on a database as a part of the creation procedure. If for some reason you can't so that, you need a process that checks sys.databases for new ones (create_date) and perform an initial backup.
The probability of survival is inversely proportional to the angle of arrival.
November 23, 2010 at 8:56 am
This might be a bit simplistic but you could use the following query to show all databases that have no database backup in the msdb.dbo.backupset table which would then show new databases since they would have had no prior full backup. So assuming that you were running log backups on all databases you would use the output here to run a full for the databases found prior to running your log backups.
SELECT b.name AS database_name
FROM MASTER.dbo.sysdatabases b
LEFT OUTER JOIN msdb.dbo.backupset a
ON a.database_name = b.name
AND a.TYPE = 'D'
WHERE a.database_name IS NULL
GROUP BY b.name
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 23, 2010 at 9:03 am
There are a few things you could do.
One would be to add a check to your hourly job for databases with no backup history.
One would be to run a backup as part of the creation script.
Another would be to use a DDL trigger to detect if a database has had any schema change, including being created, and run a backup once the DDL has committed. That might get messy if you're making a bunch of changes all at once, but it would be definite.
I'd do it as part of the creation script. That gives you the most control over the process. Then I'd add a check to the hourly maintenance job (runs the log backups), as insurance against human error in the creation script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 9:17 am
Thanks!
Some of the problems we have with backup during creation is that a different team has some scripts "they can't change". Other is daily rebuild of db's - but since we have about 9000 db's we can't pursue all projects and instead we're creating an hourly job to check the if there are any dbs without backup.
November 23, 2010 at 9:21 am
That sounds like a reasonable compromise in your situation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2010 at 3:32 am
DatabaseBackup (the ola.hallengren.com backup stored procedure) has an option for this (@ChangeBackupType = 'Y') that I recommend you to use.
It will then check if a backup can be made, before actually doing the backup.
The differential backup check is based on sys.master_files.differential_base_lsn and the log backup check is based on sys.database_recovery_status.last_log_backup_lsn.
If a backup cannot be made, it will change the backup type for that database and job execution.
So in your case someone creates a new database and then you run the log backup job. Then the backup type for the new database will be changed to Full for the first job execution after the database was created.
Ola Hallengren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply