Re: Recovery Model Question.

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@xxxxxxxxxxx> wrote in message news:eVZMFxpZHHA.1400@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx> wrote in message news:eSAEbjnZHHA.5044@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxx> wrote in message news:%23V1czMnZHHA.1400@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message news:%23DV67OlZHHA.348@xxxxxxxxxxxxxxxxxxxxxxx
Are you asking for a way to change this value against 200 different databases?

"Cipher" <cipher@xxxxxxx> wrote in message news:eIh22lkZHHA.4772@xxxxxxxxxxxxxxxxxxxxxxx
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.






.

Relevant Pages

  • Re: Recovery Model Question.
    ... We all feel differently about msdb. ... your liking". ... last database for each backup execution. ... and if somebody restore msdb and then the other databases based ...
    (microsoft.public.sqlserver.tools)
  • Re: Recovering Transaction Logs
    ... Backing up MSDB is always a good thing to do but it does not affect the way ... restore chain is that you usually only backup MSDB once a night. ... >> day so you don't have that many log files to recover. ... >>> t-log files to pick from, and I can easily click on the t-log that I ...
    (microsoft.public.sqlserver.programming)
  • Re: Automating Backups
    ... Here is the script. ... I need to set up a backup and restore accross a network ... USE msdb ... EXEC sp_add_jobstep @job_name = 'myTestBackupJob', ...
    (microsoft.public.sqlserver.msde)
  • Re: Is msdb transactional???
    ... It is true that backup history for all backups performed on an SQL Server is in the msdb database. ...
    (microsoft.public.sqlserver.server)
  • Re: Back up system DBs
    ... I prefer to do only db backup for master and model. ... I also do log backup (because of ... As Agent set msdb to simple recovery at startup, ...
    (microsoft.public.sqlserver.server)