Non-Production Servers – SIMPLE recovery?

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)
FROM sys.databases
WHERE recovery_model_desc <> 'SIMPLE'

A few features worth mentioning:

(1) change the varchar(max) to 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…)

This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

6 Responses to Non-Production Servers – SIMPLE recovery?

  1. Dirk Hondong says:

    Thank you Sir.

    This is quite useful for me right now since I have a few test environments where there´s no need to run the DBs in full recovery mode.


  2. David Scutt says:

    I think our UAT databases all use Full recovery (even though they needn’t), and the backup guys seem fine with that too. We do have some Production databases that use Simple recovery (some of our OLAP\DW databases are going that way, as they’re effectively built up each day), as well as a load where the Backup\storage team stlll haven’t worked out how to use NetBackup properly (and they run with the caveat that their recovery strategy can only go back to the last successful full backup – that’s their risk now, not mine. I’ve told them).

    It’s frustrating not being fully in control of DB backups, especially where the storage team are less than… helpful.

    Also – have you heard of the undocumented(!) sp ‘sp_msforeachdb’, which iterates through each DB and executes a command against it? I think in this case, your code is probably more elegant, but worth keeping in mind that there are other options for iterating through databases…?

    • thomasrushton says:

      David – yes, I know the sp_msforeachdb stored proc well. As you say, it’s undocumented, so its use / availability may be discontinued, or its functionality may be altered without any warning from MS. You would be advised to leave it well alone… …except for reading it as a learning exercise, of course.

      The reason I didn’t use it in this case is that I’m not running code against every database, only against those which are not already in Simple recovery. sp_msforeachdb with an IF statement wrapped into it would just feel really wrong.

      As for “that’s their risk now, not mine – I’ve told them” – have you kept the email chain? Did you make sure that their (& your) bosses know? Just checking. There was a recent blog post by Brent Ozar that might be of interest – The 9 Letters That Get DBAs Fired.

      See you back in The Other Place.

      • Dirk Hondong says:

        Just one idea regarding sp_msforeachdb:
        I remember that there´s a blog post by Aaron Bertrand
        That´s a good alternative to the system proc

      • David Scutt says:

        I just offered it up as an “it’s there” – as long as you use it with the usual caveats of undocumented\unsupported code, as you say. I can’t think of a reason I’d need to do that for any Production environments (I think I last used it for getting data out of a number of similar databases on a server). I will take a look at Aaron’s version of it, though.

        My ‘A’ is definitely covered – just double-checked for the email. I’ve always explicitly explained the consequences (in detail – short DBA & recovery course, if you like) of choosing one recovery strategy over another, so they know… I think they’ve given this hot potato to a counterpart in the US, the last I heard. There’s other political stuff behind it too, I think.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.