February 10, 2005 at 11:37 am
Good Afternoon,
I created a maintenance plan to backup the user database transaction logs every two hours. It has never worked yet. I can backup the databases but not the logs. In troubleshooting I found all my user databases recovery options set to simple. I think this is causing the logs to fail. However, when I try to change from simple to full I am unable to, The 'OK' button is grey out. I have tryed by connecting to the SQL server as myself and as sa, both with SYSADMIN fixed roles.
Any suggestion?
Thanks,
PGustafson
February 10, 2005 at 1:26 pm
I'm not sure why your OK button is unavailable. The fact that your databases are in Simple Recovery Model is definitely why your transaction log backups are failing. Simple Recovery and transaction log backups are mutually exclusive. In Simple Recovery, transactions are "thrown away" after they are committed to the database. So there's nothing to back up.
Try this in Query Analyzer -
ALTER DATABASE Pubs
SET RECOVERY FULL
If this works, I'd say the problem is with your Enterprise Manager, and it may need to be reinstalled (not SQL, just EM). If it doesn't work, I really don't know what the problem is. What version of SQL and Service Pack are you running? A service pack install might take care of it.
Steve
February 11, 2005 at 10:07 am
One additional point. After changing the 'recovery model' from 'simple' to 'full' you need to perform a full database backup immediately afterwards before any transaction log backups occur to maintain your recoverability.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply