October 9, 2007 at 6:24 am
Hi,
I've created a maintenance plan to do a backup of my transaction logs every day. Unfortunately there are some databases that refuse to have such type of logs, producing errors:
Database 'SharedServices1_DB' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Executing the query "BACKUP LOG [SharedServices1_DB] TO DISK = N'K:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\SharedServices1_DB\\SharedServices1_DB_backup_200710090201.bak' WITH NOFORMAT, NOINIT, NAME = N'SharedServices1_DB_backup_20071009020117', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OK, I could start by adjusting the plan to include only selected databases. But then I have to adjust the plan every time a new database is established. Or I could change the database but as some other app is creating these (Sharepoint) I'm not sure if this possibly break the application.
Is there a way to stay with "all databases" as selection and get rid of errors like the one stated above?
Jochen
October 9, 2007 at 8:06 am
nope. This is a major shortfall of maintenance plans.
I've used scripts instead to power backups. It's easy to find some on this site or others, which will scan for non-system databases and skip those with db options incompatible with log backups.
Here's one: http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31640/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply