Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Database Recovery Mode Expand / Collapse
Author
Message
Posted Tuesday, March 18, 2008 12:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:38 PM
Points: 247, Visits: 198
Hey Guyz,

I was wondering what TSQL Script you use to find database recovery mode?

I am using this.
SELECT CONVERT(SYSNAME, DATABASEPROPERTYEX(N'master', 'Recovery'))

Just wanted to share this with y'll and find out what others are using?

Do we have a snippet section where I can post little scripts which are useful?

Cheers,
Nirav



Kindest Regards,

WRACK
CodeLake
Post #470648
Posted Tuesday, March 18, 2008 9:38 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:54 PM
Points: 4,065, Visits: 5,296
I've been using this query:

select recovery_model_desc from sys.databases where name = 'master'

The sys.databases view is in every database.

I guess you could post your statement in the "Scripts" section. See the navigation list on the left side of the screen.


Greg
Post #470943
Posted Tuesday, March 18, 2008 11:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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


SQL DBA.
Post #471031
Posted Tuesday, March 18, 2008 6:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:38 PM
Points: 247, Visits: 198
Greg Charles (3/18/2008)
I've been using this query:

select recovery_model_desc from sys.databases where name = 'master'

The sys.databases view is in every database.

I guess you could post your statement in the "Scripts" section. See the navigation list on the left side of the screen.


Hi Greg,

Thanks for the reply. Will the script you posted also work under SQL 2000? I am not 100% sure that my script will work under SQL 2000.



Kindest Regards,

WRACK
CodeLake
Post #471303
Posted Tuesday, March 18, 2008 6:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:38 PM
Points: 247, Visits: 198
$sanjayattray (3/18/2008)
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


Hi Sanjay,

Thanks for replying. It seems to me that you are looping through the list of databases and printing the recovery model of each database.

I would normally stay away from cursor and just use the following query. I don't mean to be bashing your method, just a suggestion.

SELECT [name] AS [DatabaseName], CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel] FROM master.dbo.sysdatabases ORDER BY name



Kindest Regards,

WRACK
CodeLake
Post #471304
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse