December 11, 2003 at 2:23 pm
Greetings. I am currently using a maintenance plan to back up my database. I have one maintenance plan to back up the databases and another to back up the transaction logs. When I set it up all the backups completed successfully but at some point they started failing on the transaction logs for the master and msdb databases. Transaction logs for the other databases are completing successfully. Looking at the maintenance plan history the history indicates 'Backup cannot be performed onhe backups are being written to disk and there is a very large amount of free space on the disk. I am requesting the maintenance plan to delete backups older than one week but because the backups are failing the old backups are not being removed. I have run DBCC and find no errors. I am looking for suggestions for ways to troubleshoot this problem. SUggestions are greatly appreciated. Thanks.
December 11, 2003 at 2:55 pm
I think your error message got cut off from your post. Would you please repost your error?
Thanks.
December 11, 2003 at 5:16 pm
Both master and msdb have simple recovery model. It means that you can't backup transaction logs for those DBs (I'm wondering how it was working before). The maintenace plan wizard has an option to specify all users databases excluding master, model, msdb. Use it to setup transaction log backup.
December 12, 2003 at 2:05 am
I got caught on this one as well.
If a database is in "simple" recovery mode then the transaction log backup portion of the maintenance plan will fail.
Coming from SQL 6.5 I didn't know that sp_dboption database name,'trunc. log on chkpt.','ON' switched the recovery mode to simple.
Because the maintenance plans halt if there is an error I always set up separate maintenance plans for each user database, that way if a backup fails on one it won't halt the plans on the others.
He was not wholly unware of the potential lack of insignificance.
December 12, 2003 at 11:52 am
Thanks to all who replied. This leads to a follow up question. In that master, model and msdb are in simple recovery mode is there anything necessary to take this into account when doing a restore?
Thanks.
December 12, 2003 at 12:41 pm
With restore you have to remember that you can only restore the full backup for master, model, msdb.
The idea is that the data in system databases are pretty stable and not changed as often as in the user DBs.
The proper full backup schedule for system DBs depends on your system usage. For example, in our environment (ASP) users can create databases for themselves (sort of), couple per day. With that we backup master daily.
Microsoft strongly recommends to do full backup of system DBs before and after you apply service packs and/or hotfixes or make configuration changes.
December 12, 2003 at 2:32 pm
With simple recovery mode, only full backups are possible.
When failure, you will have to restore the last full backup you have.
With system databases this is ok. I would suggest to also backup the system databases each time you make a change in the
December 17, 2003 at 7:01 am
quote:
Thanks to all who replied. This leads to a follow up question. In that master, model and msdb are in simple recovery mode is there anything necessary to take this into account when doing a restore?Thanks.
IMO a restore of the master or msdb database is convoluted. It would be wise to practice the process on a Test server before an actual problem arises on a Production server. If you are interested, I can e-mail you a copy of the notes (4 pages in Microsoft Word format) I made while testing, or I can paste them here. Be aware that you can also restore master or msdb from an NT backup if SQL Server was stopped during your NT backup, but Microsoft prefers that you restore from a SQL backup instead.
December 17, 2003 at 7:17 am
Perhaps you should write an article and submit it to the site.
Enlighten us all and earn US$25 in the process.
========================
He was not wholly unware of the potential lack of insignificance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply