Technical Article

Quickly Reveal DB Recovery Model on all Databases.

,

I wrote this little tool out of the frustratingly slow process one must endure to find out what database Recovery Model your SQL 2000 databases are currently set to. This tool will quickly reveal whether your database Recovery model is set to either "Simple", "Full", or "Bulk-Logged."

Who has time to manually pull up "Properties" then "Options" on each database? Not us! Let's put an end to this problem!

OK - this script automatically writes and executes the necessary scripts to quickly reveal all of your database Recovery Models on all databases on a machine. Just drop it in Query Analyzer on any SQL 2000 or SQL 2005 machine - run it - and enjoy! This tool utilizes the SQL 2000 Transact-SQL function DATABASEPROPERTYEX. This tool will NOT work on SQL 6.5 or SQL 7.0. Also you will need to have full System Administrator privileges to execute this script. This script reveals information only. It does NOT alter or modify any database setting.

Here is my DBRecoveryModelGenerator Tool:

Use master
declare @DBName varchar(35),
             @str varchar (255)
declare DBRecoveryModelGenerator_cursor cursor for
 select name from sysdatabases
where category in ('0', '1','16') 
order by name
open DBRecoveryModelGenerator_cursor
fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @str = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName 
exec (@str)
end fetch next from DBRecoveryModelGenerator_cursor into @DBName end
close DBRecoveryModelGenerator_cursor
DEALLOCATE DBRecoveryModelGenerator_cursor
go

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

Related content

Technical Article

Recompile Views/SPs from DOS from .SQL Files.

This script is very helpful for anyone trying to recreate particular SQL Server Database Versions for Testing.  It allows you to automatically recompile any stored procedures or views saved with drop, create and permission statements.  The scripts must all be sitting in one directory and end in the same extension.  I have found this very […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-25

684 reads

Technical Article

Send notifications on job STEP failures (trigger)

Have you ever wanted to have SQL note a failure on a step, yet continue running the job.  I found it to be annoying that you have to fail (and end) a job in order to send a message to an operator.  I have processes that run at off times, I want to know when […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

2002-02-13

2,267 reads

Technical Article

Stored Procedure to read tape backup log

If you need to check that the tape back of your database backup files was successful, you would generally read the errorlog for the backup software.If you are just interested in today's results or a particular day's log it can be a pain to get.  You could have a shortcut on the desktop or you […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (1)

You rated this post out of 5. Change rating

2002-02-10

370 reads

Technical Article

Transaction Logs - Job Script

I've included this job script, because it is used by the scheduler scripts that I've also posted. This job is a little more involved than most may need... It first checks to see if certain jobs are running. Is so, it waits for a while, and then checks again. After that it drops the existing […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-01-04

1,119 reads