March 18, 2008 at 10:53 am
I am changing the recovery model of the db to Full before taking transaction log backup, sometimes the recovery model does'nt change from Simple to Full resulting in the failure of the tran log backup's on that db, inspite of adding the below code as the first step in the sql job:
SQL code:
DECLARE @Mode sql_variant
SELECT @Mode = DATABASEPROPERTYEX('DBname', 'Recovery');
IF @Mode = 'SIMPLE'
ALTER DATABASE DBname
SET RECOVERY FULL;
In the tran log backup sql job, I have the above code as step1 and then in step2: tran log backup.
what can cause the db not to alter to Full? The err in the event log does'nt give much info.
Note: The reason I am trying to alter the recov model is bcause of one of the application's stored proc runs randomly and changes the recov model to Simple.
Please advice.
March 18, 2008 at 11:22 am
If I ever alter the recovery model to Full from Simple the first thing I do is take a full backup, before even thinking of taking a log backup, simply because until the change there would not be any relevant/consistent data in the log, as logging had been switched off by the use of the simple recovery model. So the full backup is required to make your log backups relevant.
March 18, 2008 at 11:26 am
After you change the recovery to Full, do you take a full backup/differential backup before taking the transaction log backup?
March 18, 2008 at 11:40 am
Jo Pattyn (3/18/2008)
After you change the recovery to Full, do you take a full backup/differential backup before taking the transaction log backup?
After changing the recovery to Full, I take tran log backup. Currently the way it's been set up by our old DBA: Full db backup @night and tran log backup's every 4 hours. I shall try to change to the new way which is:
every sunday: Full db backup
Other days: Diff backup's of the database
Trans log backup's: will be every 30 mins or 1hr
And also when I am trying to alter the recov model why's it's not changing to Full?? Thanks!!
March 18, 2008 at 1:45 pm
Mh (3/18/2008)
Jo Pattyn (3/18/2008)
After you change the recovery to Full, do you take a full backup/differential backup before taking the transaction log backup?After changing the recovery to Full, I take tran log backup. Currently the way it's been set up by our old DBA: Full db backup @night and tran log backup's every 4 hours. I shall try to change to the new way which is:
every sunday: Full db backup
Other days: Diff backup's of the database
Trans log backup's: will be every 30 mins or 1hr
And also when I am trying to alter the recov model why's it's not changing to Full?? Thanks!!
I'm curious why you need a script to keep switching it to Full? Is something switching it back? Like has been mentioned before - the minute you switch from Full to simple - your log backups are "garbage" since the transaction log chain was broken.
If you can -find a recovery model and stick with it. Someone else mentioned databases magically switching recovery models on them - something about a monitoring tool "deciding" to change their recovery. In any case - find the culprit and terminate with prejudice. You should be the only one in charge of what the recovery model is.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 2:04 pm
Carolyn Richardson (3/18/2008)
If I ever alter the recovery model to Full from Simple the first thing I do is take a full backup, before even thinking of taking a log backup, simply because until the change there would not be any relevant/consistent data in the log, as logging had been switched off by the use of the simple recovery model. So the full backup is required to make your log backups relevant.
Since in Simple recovery model, all the committed transactions are deleted from the datafile, so it does'nt allow tran log backups to be taken right. In my case since I am changing the recovery model from Simple to Full, why I should take the full db backup first, and then the tran log backup's?? Actually we are already taking full db backup every night. Thanks in advance!!
March 20, 2008 at 2:24 pm
See http://msdn2.microsoft.com/en-us/library/aa173551(SQL.80).aspx
"Restoring a database using both database and transaction log backups works only if you have an unbroken sequence of transaction log backups after the last database or differential database backup. If a log backup is missing or damaged, you must create a database or differential database backup and start backing up the transaction logs again. Retain the previous transaction logs backups if you want to restore the database to a point in time within those backups."
Your change to the recovery model has effectively broken the sequence as for the period you were in simple mode meant no logs were being kept.
March 21, 2008 at 2:23 pm
Thanks, I shall take full db backup and then subsequent tran log backup's after changing the recovery model from Simple to Full.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply