Altering recovery model of db before tran logbkup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Altering recovery model of db before tran logbkup

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-13 : 14:49:21

We are using desktop authority application, the recovery model of the DAReporting DB which belongs to that app keeps changing from Full to Simple due to which the tran log backup's are failing on that db. So I did the follow:
1. First created db maint plan on that db using the maint plan wizard for tran log backup.
2. Then added a step to that tran log backup job, to alter the db recovery model to Full incase the recovery model is Simple and made this as step1.
3. Then step2 is the tran log backup.
Please let me know if this is okay to change the recovery model of the db in the SQL job before the tran log backup. Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-13 : 16:57:19

Sounds fine to me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-18 : 12:57:59

Sometimes the recovery model does'nt change to Full, resulting in failure of the tran log backup's on that db. The error in the event log does'nt say much about that. please advice

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-18 : 13:49:21

What is the error for the job step when it doesn't change it to FULL?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-18 : 15:00:55

Here is the err from the event log:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 3/18/2008
Time: 2:00:03 PM
User: N/A
Computer: Servername
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan-DAReportingDB'' (0x771CBE41C9272949A4176D485A22C991) -
Status: Failed - Invoked on: 2008-03-18 14:00:02 -
Message: The job failed. The Job was invoked by Schedule 139 (Schedule 1).
The last step to run was step 4 (NotifyTranlogbackupJobfailure).
Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-18 : 18:36:28

What I was asking for is the error from the ALTER DATABASE statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-19 : 11:18:57

Hi Tara,

Thanks for your response, I think I figured it out, within the SQL job the starting step was Tran log backup sql, so I made the starting step as "AlterRecoveryModel", then next step as Tran log backup, it's my bad. Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources