Recovery Model Question.

Hello to everyone,
I want to ask if there is a way ,maybe through a strored procedure or sql
script
to change massive in 200 databases for example the recovery model from full
to simple ?
If anybody has an idea or knows i will appreciate it.
Thanx a lot.
Cipher.
See ALTER DATABASE in BOL
"Cipher" <cipher@.aol.com> wrote in message
news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hello to everyone,
> I want to ask if there is a way ,maybe through a strored procedure or sql
> script
> to change massive in 200 databases for example the recovery model from
> full to simple ?
> If anybody has an idea or knows i will appreciate it.
> Thanx a lot.
> Cipher.
>
|||Are you asking for a way to change this value against 200 different
databases?
"Cipher" <cipher@.aol.com> wrote in message
news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hello to everyone,
> I want to ask if there is a way ,maybe through a strored procedure or sql
> script
> to change massive in 200 databases for example the recovery model from
> full to simple ?
> If anybody has an idea or knows i will appreciate it.
> Thanx a lot.
> Cipher.
>
|||Yes,exactly.
I want to change massive all the 200 databases recovery
model option from full to simple but i dont want to achieve this manually
because of the huge overhead.
For that reason i asked ,if theres a more quicker and better way to achive
this.
Thnx a lot.
Cipher.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23DV67OlZHHA.348@.TK2MSFTNGP02.phx.gbl...
> Are you asking for a way to change this value against 200 different
> databases?
> "Cipher" <cipher@.aol.com> wrote in message
> news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
|||Tibor,
I will even keep MSDB in simple recovery mode and takes the differential
backup twice a day.
SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
FROM sys.databases
WHERE name NOT IN('master', 'tempdb','MSDB')
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSAEbjnZHHA.5044@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> No version posted, assuming 2005:
> Modify below to your liking, run it, take the result, tidy up and verify,
> and execute it.
> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases
> WHERE name NOT IN('master', 'tempdb')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Morgan" <morgan@.heaven.com> wrote in message
> news:%23V1czMnZHHA.1400@.TK2MSFTNGP06.phx.gbl...
|||First,thnx a lot guys for your support.
I run this sql script through Query Analyzer :
use master;
select 'alter database' +name+ 'set recovery simple'
from dbo.sysdatabases
where name not in('master','tempdb','msdb','model')
go
but the weird is that the script runs succefully without errors ,but it
didnt make any change
to any databases!I check the recovery model and remains unfortunately full
in all databases..
i dont understand..
Also because i forgot to mention it,the SQL version is SQL 2000 SP 4
Cipher.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVZMFxpZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Tibor,
> I will even keep MSDB in simple recovery mode and takes the differential
> backup twice a day.
> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
> FROM sys.databases
> WHERE name NOT IN('master', 'tempdb','MSDB')
> Thanks
> Hari
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eSAEbjnZHHA.5044@.TK2MSFTNGP05.phx.gbl...
>
|||Yes,it works fine now.
The script achieve a massive recovery model change!
Thx a lot Tibor!
Cipher.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKlFswtZHHA.4808@.TK2MSFTNGP04.phx.gbl...
> The script generates your ALTER DATABASE commands for you. Take the result
> of the script, paste in into a query window and execute it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cipher" <cipher@.aol.com> wrote in message
> news:eZOTxetZHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Tibor,
I agree with you...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJpY2gtZHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Hi Hari,
> We all feel differently about msdb. That is why I said "Modify below to
> your liking". I prefer to do log backups for msdb, and have msdb as the
> last database for each backup execution. This way I always have backup of
> the most recent backup history. I might not be the one who is doing the
> restore, and if somebody restore msdb and then the other databases based
> on backuphistory, they won't get the databases as up to date as the
> backups would allow. Many find this overkill, though. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVZMFxpZHHA.1400@.TK2MSFTNGP06.phx.gbl...
>

Recover HUGE DB

Hi..

As a relative novice at disaster recovery, I have recently inherited a massive DB as a new member to an IT co. of approximate 50Gb, and the sql server took a dive, unfortunately due to the size, there has been no backups (broken rule 1,2,3...99), like I say inherited

The sql start-up reports the DB as recovering; however no disc or cpu usage suggest its not doing much, its been left for 3 days to recover and appears to have failed; no errors in logs, I have various MDF files and LDF files no NDFs, suggests its happened before.

I have a small box being assembled which I will transfer all the MDF/LDF files to, with SQL server 2000 on, The original is only SQL 7.

I need the quickest way to get the DB as close to active on the dummy box to extract some of the SPs, not so bothered with the data, the actual SQL server appears damaged, as it will not allow creation of DBs complains about exclusive access to the model db (original)?

Many Thanks in advance

ChrisHowdy

You can try attaching the MDF file to the new box.

Let us know how it goes.

Cheers,

SG|||Originally posted by sqlguy7777
Howdy

You can try attaching the MDF file to the new box.

Let us know how it goes.

Cheers,

SG

Yep,

I had to copy the MDF, LDF files to a NAS storage unit (it took 5 hrs, darn thing), i destroyed the instance of SQL by removing it completely, i re applied windows NT4 sp5 and Re-Installed Sql 7, however the db file would not reattach; it just kept going, never stopped i left it two days to try, but gave up when the processor usage tailled off.
I gave up on Sql 7 and built a machine with windows 2k and Sql 2000 on attached the database as a single file (without logs) and had to DTS all the data and SPs, back to the original server which i had rebuilt,
I have a lot more faith now in 2000 than 7, however this was a really slow turnaround.