How to change from a simple recovery model to Full

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 2005 Forums

SQL Server Administration (2005)

How to change from a simple recovery model to Full

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2010-01-06 : 23:23:51

Hello All,

I have 10 databases on an SQL Server 2005 instance, and what I want is to change at least 3 of those databases to a full recovery model with transactional backup. The transactional backup is as follows:
- 1st database I want to perform a transactional backup at 9am, 12pm and 2pm
- 2nd database I want to perform a transactional backup at 9am, 12pm and 5pm
- 3st database I want to perform a transactional backup at 9am, 11am, 1pm, 3pm, 5pm and 9pm

Concerns:
I have a maintenance plan that backs up all the 10 databases and saves it in a sub-directory for each database. What would happen once I change to the full recovery model? Will the select databases which have transactional backup be saved in the same sub-directory where the full backups are kept?

Questions:
How to change from a simple recovery model to Full recovery model for the 3 of the 10 database?

Please advice.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-06 : 23:51:06

It is likely you'll need re-setup your maintenance plan.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-07 : 00:36:46

to change 'em

ALTER DATABASE yourDB SET RECOVERY FULL

I don't use maintenance plans, but it looks like you're going to need multiple plans. All the more reason to abandon them altogether and write your own custom scripts and SQL Agent jobs. Post back if you need help doing so.