Recovery Model What Database/table stores info

2 Sep 2005 3:21 PM

Lontae Jones

What database/table stores information on the recovery models used for all
database on an SQL server? I want to change all to FUll. Thanks

2 Sep 2005 3:31 PM

Aaron Bertrand [SQL Server MVP]

This will create a script. Run it in query analyzer, then copy the lower
pane, paste to the top, and run again:

SELECT 'ALTER DATABASE '+name+' SET RECOVERY FULL'
FROM master..sysdatabases
WHERE name NOT IN ('master','tempdb','msdb','model')

Show quote

"Lontae Jones" <LontaeJo***@discussions.microsoft.com> wrote in message
news:1B6EC27B-F874-4051-90DE-C21338E393EE@microsoft.com...
> What database/table stores information on the recovery models used for all
> database on an SQL server? I want to change all to FUll. Thanks

2 Sep 2005 3:46 PM

Alejandro Mesa

Use "alter database" instead. Copy the result of the following "select"
statement annd execute it in QA.

select
    'alter database ' + quotename(catalog_name) + 'set recovery full'
from
    information_schema.schemata
where
    catalog_name not in ('master', 'model', 'tempdb', 'msdb', 'pubs',
'northwind')
    and databasepropertyex(catalog_name, 'Recovery') != 'FULL'
go

AMB

Show quote

"Lontae Jones" wrote:

> What database/table stores information on the recovery models used for all
> database on an SQL server? I want to change all to FUll. Thanks

2 Sep 2005 3:58 PM

Aaron Bertrand [SQL Server MVP]

> from
> information_schema.schemata

FWIW, this no longer works the same in SQL Server 2005. I think it's the
only Information_schema view that breaks backward compatibility, but in
truth, it currently doesn't work the way it should anyway. SoI have been
shying away from recommending the schemata view specifically, because its
useful lifetime is rather short.

A

2 Sep 2005 3:59 PM

JT

Do we have a schema database diagram poster for 2005 yet?

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23Kj03b9rFHA.3340@TK2MSFTNGP15.phx.gbl...
>> from
>> information_schema.schemata
>
> FWIW, this no longer works the same in SQL Server 2005. I think it's the
> only Information_schema view that breaks backward compatibility, but in
> truth, it currently doesn't work the way it should anyway. SoI have been
> shying away from recommending the schemata view specifically, because its
> useful lifetime is rather short.
>
> A
>
>
>

2 Sep 2005 4:08 PM

Aaron Bertrand [SQL Server MVP]

Gosh no

Show quote

"JT" <some***@microsoft.com> wrote in message
news:%23zYU1e9rFHA.904@tk2msftngp13.phx.gbl...
> Do we have a schema database diagram poster for 2005 yet?

2 Sep 2005 4:08 PM

Alejandro Mesa

Aaron,

Thanks for sharing that.

AMB

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > from
> > information_schema.schemata
>
> FWIW, this no longer works the same in SQL Server 2005. I think it's the
> only Information_schema view that breaks backward compatibility, but in
> truth, it currently doesn't work the way it should anyway. SoI have been
> shying away from recommending the schemata view specifically, because its
> useful lifetime is rather short.
>
> A
>
>
>
>