Speaking of putting databases into SIMPLE recovery mode…
This post is based on what I use on non-production (eg for QA, or UAT) replicas of production environments – the sort of thing where you need the data, but don’t need to maintain it or do backups.
If you have to set up this sort of environment regularly, then the chances are that you’re doing it by restoring database backups from production. These databases will almost certainly be in “FULL” recovery so you are able to recover in the event of a disaster. (You *are* remembering to backup your transaction logs, right?) If you leave a database in “FULL” recovery, but don’t schedule a regular transaction log backup, then you will find that the transaction log grows. And grows. And eats your hard disk (I’m assuming that you’ve kept the disk space tight on these environments, because your storage guys won’t give you enough storage to directly match production… mine didn’t.)
If you build this environment by restoring databases from production, as well as restoring the data, you’re also restoring the database settings from within the database, and one of those is the recovery model. Which is most likely to be “FULL” recovery.
So what I do is implement a scheduled job on each of these servers to run the following little bit of SQL every night:
declare @SQL varchar(max)
SELECT @SQL = @SQL + 'alter database [' + name + '] SET RECOVERY SIMPLE'
+ char(13) + char(10)
WHERE recovery_model_desc <> 'SIMPLE'
A few features worth mentioning:
(1) change the
varchar(8000) for SQL Server 2000 – this should be enough, unless you’ve got a *huge* number of databases to deal with. Anyway, why are you still running SQL Server 2000?
(2) Rather than looping through every database with a CURSOR or a WHILE loop, we’re using a single SELECT statement to build up a string containing the appropriate commands.
(3) we’re only looking at the databases that aren’t already in SIMPLE recovery, so we won’t get error messages for trying to set master or tempdb to SIMPLE.
(4) If you have strangely-named databases (eg anything with a “-” or a space), you’ll probably need the square brackets shown above
(5) Note also that the EXEC command has the @SQL parameter in parentheses. This is a classic “Gotcha” brought about by EXEC having two sets of syntaxes – without parentheses (for calling a stored procedure), or with parentheses (for passing a string to be executed). If you get that wrong and omit the parentheses from the Exec (@SQL) line, then you’ll see an error message such as this:
Msg 203, Level 16, State 2, Line 10
The name 'alter database [DBName1] SET RECOVERY SIMPLE
alter database [DBName2] SET RECOVERY SIMPLE
' is not a valid identifier.
Job done. One less thing to worry about (or should that be “one fewer thing (or things) to worry about”? Damn. Now I’m worrying about that instead…)