change all db's to simple, and shrink all log files.

Last Post 22 Mar 2007 01:18 PM by sqladmin. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqladmin
New Member
New Member

--
21 Mar 2007 01:35 PM
search terms:
simple recovery
shrinkfile
set recovery simple
all databases

suppose you have a hundred databases on a dev, or test server,
and what you need to do is change each and every one to simple
recovery model, and shrink all log files excluding system db's

if you have a situation like this try this little script.

before you run this make sure your output is in Text not Grid
and you can do this by pressing: CTRL-T

paste this in:

------------------------------------------------------------
set nocount on

select 'use master' + char(10) + 'go' + char(10) +
'alter database [' + [name] + ']' + char(10) +
'set recovery simple' + char(10) +
'use [' + name + ']' + char(10) + 'go' + char(10) +
'dbcc shrinkfile (2, 8)' + char(10) + 'go'
from master..sysdatabases where [dbid] >4


------------------------------------------------------------

run it, and just take the output, and run it against
your server.

it will change all databases excluding master & tempdb system db's of course.

hope it's useful.

cheers


SQLUSA
New Member
New Member

--
22 Mar 2007 11:08 AM
Nice.

How about going undocumented:

create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union all select 'msdb'
union all select 'model'
union all select 'tempdb'

exec sp_MSforeachdb @command1 =
'if not exists (select * from #systemdbs where name = ''?'') select ''This is database: ?'''


drop table #systemdbs

Kalman Toth
SQLUSA: http://www.sqlusa.com
sqladmin
New Member
New Member

--
22 Mar 2007 01:18 PM
Cool.
You are not authorized to post a reply.

Acceptable Use Policy