February 1, 2005 at 3:01 am
I have about 450 databases running on my SQL 2000 server which are all Exact Globe 2003 administration. They are using a bit to much HD space at the moment and I was suggested that I should change de recovery option from full to simple. However doing this manualy for 450 databases is gonna cost my a long time to do. Is there a way to script this ?
February 1, 2005 at 3:06 am
You are aware that if you change the recovery mode from FULL to Simple that you will not be able to backup the transaction logs?
If you have a disaster you will have to revert to the last good FULL backup.
February 1, 2005 at 3:17 am
Yes I indeed know that now LOL but since I make a full back-up every night and the HD space problem is getting worse and worse until I get some new storage space it'll have to do I am afraid.
I am not really a big fan the idea either but I have little choice left.
February 1, 2005 at 3:26 am
I know I'm not answering the question you posted but do you have spare disk capacity on another server? If you do and your SQL Server Service is running under a domain account you could backup some of your databases to this server?
Just another question.
How many days worth of backup and transaction logs do you keep for each database?
February 1, 2005 at 3:35 am
Well to answer your first question I don't have spare servers which have that much space left, infact this server is brand new as it replaced our old SQL server. Our space problem will not occure before the end of the year but I like to try and beat it before it ever becomes a problem. (You know those sales people are never telling the truth "will this database grow any larger? Oh no sir just 10% or so" it sits on a nice 200% at the moment lol)
The second answer to your question is a bit hard tought because I have not set my mind on it yet.
February 1, 2005 at 4:51 am
To answer your original question.
I don't know a way of setting the recovery model of multiple databases.
If I were you I would try and find out which databases are the least mission critical and set these to Simple. I would do all I could to ensure that the mission critical databases are set to FULL so you can ensure point in time recovery.
Other than that; you need more disk space
February 1, 2005 at 5:09 am
Hello,
what I suggest you is to create cursor with your DB names and to run DATABASEPROPERTYEX( database , property ) function inside that cursor
February 1, 2005 at 12:36 pm
DATABASEPROPERTYEX tells you what the property is rather than changing it.
If you decide you want to programmatically change to the simple recovery model,
you can do this by creating a cursor for your database names (as Vidas suggested) (don't forget to exclude the system databases and Pubs and Northwind), and then code an ALTER DATABASE statement inside your loop. I would suggest that rather than executing the ALTER within the loop, that you create ALTER statements in the output of the script, that you can then cut and paste into a query window and execute...
declare @db sysname,
@text varchar(500)
declare db_cur cursor for
select name
from master..sysdatabases
where name not in ('master', 'msdb', 'model', 'tempdb', 'pubs', 'northwind')
open db_cur
fetch next from db_cur into @db
if @@fetch_status = -1
begin
close db_cur
deallocate db_cur
end
else
begin
while @@fetch_status <> -1
begin
SET @text = 'ALTER DATABASE ' + @db + ' SET RECOVERY SIMPLE
GO'
PRINT ' '
PRINT @text
fetch next from db_cur into @db
end -- while
close db_cur
deallocate db_cur
end -- if
-- Steve
February 1, 2005 at 1:08 pm
Thanx Guys I am gonna try this out tommorow first thing I'll do!
February 1, 2005 at 1:36 pm
Just a quick note, this will give you all the commands that you need to run, you can then remove any databases that you don't want to change the recovery model on...
sp_msforeachdb "print 'alter database ? set recovery simple;'"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply