Change Recovery model through T-SQL

I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.
You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:

> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:

> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>