convert from FULL to SIMPLE multiple databases

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

General SQL Server Forums

New to SQL Server Programming

convert from FULL to SIMPLE multiple databases

Author

Topic

meadsld
Starting Member

3 Posts

Posted - 2008-06-26 : 23:13:09

I have like 100 small databases on my MSSQL 2005 server, all were created in FULL recovery mode. I want to change them all to SIMPLE recovery mode… is there a simple way to do this all at once?

Thanks, Mike!

meadsld
Starting Member

3 Posts

Posted - 2008-06-26 : 23:20:56

and if I convert from FULL to SIMPLE recovery... is the transaction log used at all? can I just delete it after making this change?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-26 : 23:23:02

Why are you changing all databases to Simple RM.You won't be able to recover point in time if you do that.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-26 : 23:23:57

See about Recovery model in Books online.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 23:58:45

Yes the transaction log is used with simple recovery model. No you can not delete it after making the change.

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

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-06-27 : 01:54:47

Changing from Full to Simple is not always bad , they might all be Development databases. You could easily write a script , that runs through and dynamically issues the - sql command

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

meadsld
Starting Member

3 Posts

Posted - 2008-06-27 : 10:19:45

sodeep:Why are you changing all databases to Simple RM.You won't be able to recover point in time if you do that.

Yes, I know that. I don't need point in time recovery. I only need to be able to restore to the last previous full backup (performed nightly).

sodeep: See about Recovery model in Books online.

Well... gee, why have a forum then... I started in the books online, I didn't find my answer there... hence posting my question here.

tkizer: Thanks for answering that question!

jackv: yep! but it's that "writing the script that dynamically issues the sql command" piece I'm looking for help with! :)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 10:40:26

Try this :

SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY SIMPLE'
FROM sys.databases
WHERE name NOT IN('master','tempdb','msdb','model')

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-06-27 : 11:28:53

and then execute the output from the previous post. (don't just look and admire it