October 6, 2005 at 10:02 am
I was hoping someone here could point me in the right direction. Is there anywhere in MS SQL 2000 to set the default recovery model to Simple? If not, does anyone know where I can fnid a script that will set all databases to Simple Recovery Model on a regular basis. I have a lot of new DB's created on a regular basis, and right now they're created with FULL Recovery Model. Eventually, the log files grow out of hand and I have to manually convert them to Simple and do a shrink to get the log file sizes down.
Any help would be greatly appreciated!
October 6, 2005 at 11:02 am
create a sql job and have the first step execute this code:
--set recovery model
Set quoted_identifier off
use master
go
DECLARE @dataname varchar(100)
DECLARE datanames_cursor CURSOR FOR SELECT '['+name+']' FROM sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
EXEC("ALTER DATABASE " + @dataname + "SET RECOVERY simple")
FETCH NEXT FROM datanames_cursor INTO @dataname
END
DEALLOCATE datanames_cursor
The next job step will be:
--shrink bases
Set quoted_identifier off
use master
go
DECLARE @dataname varchar(300)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT '['+name+']' FROM sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
exec ("DBCC SHRINKDATABASE (" + @dataname + ")")
FETCH NEXT FROM datanames_cursor INTO @dataname
END
DEALLOCATE datanames_cursor
I use this to set recovery model and shrink dev bases
October 6, 2005 at 11:26 am
Awesome!!! Thanks a ton for the help!
October 6, 2005 at 11:45 am
Uhm, maybe not what you are looking for, but why are there a lot of databases being created without the DBA performing the action (I am assuming you are the DBA)? Why do you need them to be in simple mode? Do not answer "to keep the log files from growing", because that is handled by backing up the log.
October 6, 2005 at 6:45 pm
Well, it's a shared hosting environment and with hundreds of MS SQL DB's and counting, the log files get out of hand and start using up ridiculous amounts of web space (seeing log files over 1GB isn't unusual).
Setting everything to simple will take care of the problem immediately. Customers always have the last night's backup to restore if something goes wrong. If they want transaction-level restores, then they need their own box.
October 6, 2005 at 11:58 pm
You can also define Simple for new databases by modifying this setting on the model database (the basis for all new databases).
Andy
October 7, 2005 at 8:16 am
THAT is great to hear. Outstanding! Thanks!!
October 7, 2005 at 9:13 am
A cursor-less way to generate all your ALTER DATABASE statements would be:
select
'ALTER DATABASE ' + rtrim(name) + ' SET RECOVERY SIMPLE'
from master.dbo.sysdatabases
where databasepropertyex(name, 'Recovery') <> 'SIMPLE'
You could save the results in a variable and run the commands with EXEC(), but I prefer to see the commands printed and manually copy them to a new window for execution. At least until I'm convinced it's safe to run automatically.
Anything else you routinely need to do to new databases (creating standard users & roles, for instance) should also be done to the model database.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply