Changing the Recovery model of the database using scripts

Hi,
I am working as a production support for SQL Server.
I dont have access to use Enterpriese manage in this server,
Please help me
To Change the recovery model of master database using scripts.
It will be great if u provide me the scripts
Its very urgent !!!!!
Thanks in advance
Regards,
SaranThe master DB uses the simple recovery model. It can't be changed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Saran" <csesaravana@.gmail.com> wrote in message
news:1148643864.847674.288290@.j33g2000cwa.googlegroups.com...
Hi,
I am working as a production support for SQL Server.
I dont have access to use Enterpriese manage in this server,
Please help me
To Change the recovery model of master database using scripts.
It will be great if u provide me the scripts
Its very urgent !!!!!
Thanks in advance
Regards,
Saran|||Hi Saran,
ALTER DATABASE Master SET RECOVERY FULL
EXEC sp_helpdb 'master'
Why do you want to change master recovery model?
HTH
Ami
"Saran" <csesaravana@.gmail.com> wrote in message
news:1148643864.847674.288290@.j33g2000cwa.googlegroups.com...
> Hi,
> I am working as a production support for SQL Server.
> I dont have access to use Enterpriese manage in this server,
> Please help me
> To Change the recovery model of master database using scripts.
> It will be great if u provide me the scripts
> Its very urgent !!!!!
> Thanks in advance
> Regards,
> Saran
>|||Hi Ami,
Thanks for your mail and it worked fine!!
We have a standard of having all the databases in Full recovery mode,
I am working for production support here , we need to work on the CMR ,
IMR to solve the tickets .
Thanks,
Saran|||The reason for having full recovery model is when you want to perform transa
ction log backup for
that database. You cannot do log backup for master, event if it is in full r
ecovery model, it still
*behaves* as when it is in simple recovery model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Saran" <csesaravana@.gmail.com> wrote in message
news:1148652299.920759.64910@.i39g2000cwa.googlegroups.com...
> Hi Ami,
> Thanks for your mail and it worked fine!!
> We have a standard of having all the databases in Full recovery mode,
> I am working for production support here , we need to work on the CMR ,
> IMR to solve the tickets .
> Thanks,
> Saran
>

Changing Recovery Type db_option for all Databases on server

I have about 29 servers that I need to set all db_options for, and they will
be the same on all servers.
Do you know the syntax for changing all database recovery types to "simple"?
Also, I need to change all db_options for AutoShrink to "false". I don't
want to go one by one, so I was hoping someone knows a shortcut.
Thanks,
Michelle-- undocumented, unsupported!
EXEC sp_MSForEachDB 'IF ''?'' != ''TEMPDB'' EXEC(''ALTER DATABASE [?] SET
RECOVERY SIMPLE'')'
http://www.aspfaq.com/
(Reverse address to reply.)
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:6B34E6BF-DF65-40C6-9623-07E3B7D0AA48@.microsoft.com...
> I have about 29 servers that I need to set all db_options for, and they
will
> be the same on all servers.
> Do you know the syntax for changing all database recovery types to
"simple"?
> Also, I need to change all db_options for AutoShrink to "false". I don't
> want to go one by one, so I was hoping someone knows a shortcut.
> Thanks,
> Michelle|||JaneDoe wrote:
> I have about 29 servers that I need to set all db_options for, and
> they will be the same on all servers.
> Do you know the syntax for changing all database recovery types to
> "simple"? Also, I need to change all db_options for AutoShrink to
> "false". I don't want to go one by one, so I was hoping someone
> knows a shortcut.
> Thanks,
> Michelle
Look at ALTER DATABASE in BOL:
SET RECOVERY SIMPLE
SET AUTO_SHRINK OFF
David Gugick
Imceda Software
www.imceda.com|||Deploy a SP on each server (perhaps in master database) that does what you
need. Then have one script that makes a remote call to each server. Also,
perhaps a WSH script that uses DMO to loop through each server, chaning
context and executing commands.
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:6B34E6BF-DF65-40C6-9623-07E3B7D0AA48@.microsoft.com...
> I have about 29 servers that I need to set all db_options for, and they
will
> be the same on all servers.
> Do you know the syntax for changing all database recovery types to
"simple"?
> Also, I need to change all db_options for AutoShrink to "false". I don't
> want to go one by one, so I was hoping someone knows a shortcut.
> Thanks,
> Michelle

CHanging Recovery Options SQL 2000

Howdy,

I want to run a Re-Indexing job against a database that normally has Tran Log backups ( it uses FULL recovery mode ) on SQL 2000. But when this job runs, it causes HUGE tran logs and huge tran log backups ( which then use up 99% of our disk space).

The job needs to be run, so I thought of changing recovery mode to SIMPLE, then run the job , then changing the recovery mode back to FULL. .........Problem solved, you would think.....

But, if the database were to corrupt after the job ( i.e. hours later ) where do I stand with what was in the tran logs both before the revovery mode change and then after? i.e. when I change to SIMPLE does the contents of the tran log get written to the db
or what?

By the way, we normally run tran log backups until 8am-6pm then do the re-indexing job at 4am and re-start the tran log backups at 8am.

Thanks for any help,

SGSafest way would be to backup the DB, change the recov mode, run the reindex, change recov mode back and back the db up again.

Never read it anywhere but pretty sure if you change the recov mode it would checkpoint the tran log.

HTH

changing recovery model to databases : replication

Using transactional replication and wanted to know if we change the recovery
model from simple to full or vice versa while replication is in place, does
it break anything ? Using SQL 2000
Thanks
nope, change away with no impact. However with full you will have to
maintain your log with more frequent dumps especially if your server is
under high load.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uUrkgx54EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Using transactional replication and wanted to know if we change the
> recovery
> model from simple to full or vice versa while replication is in place,
> does
> it break anything ? Using SQL 2000
> Thanks
>

changing recovery model

Do I need to re-cycle the server when changing the recovery model from full
to bulk logged? I can't find anything in the newsgroup or in the books onli
ne.It is NOT necessary to cycle the server when changing recovery models.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"steve harris" <anonymous@.discussions.microsoft.com> wrote in message
news:968EB502-2357-4B76-A8AF-DE5839BFC9CE@.microsoft.com...
> Do I need to re-cycle the server when changing the recovery model from
full to bulk logged? I can't find anything in the newsgroup or in the books
online.|||In addition to Geoff's remark:
You might want to do a transaction log backup before you change the recovery
model from full to bulk_logged.. If the transaction log contains any bulk
logged transactions, you won't be able to do a point-in-time restore.
Jacco Schalkwijk
SQL Server MVP
"steve harris" <anonymous@.discussions.microsoft.com> wrote in message
news:968EB502-2357-4B76-A8AF-DE5839BFC9CE@.microsoft.com...
> Do I need to re-cycle the server when changing the recovery model from
full to bulk logged? I can't find anything in the newsgroup or in the books
online.

changing recovery model

Do I need to re-cycle the server when changing the recovery model from full to bulk logged? I can't find anything in the newsgroup or in the books online.It is NOT necessary to cycle the server when changing recovery models.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"steve harris" <anonymous@.discussions.microsoft.com> wrote in message
news:968EB502-2357-4B76-A8AF-DE5839BFC9CE@.microsoft.com...
> Do I need to re-cycle the server when changing the recovery model from
full to bulk logged? I can't find anything in the newsgroup or in the books
online.|||In addition to Geoff's remark:
You might want to do a transaction log backup before you change the recovery
model from full to bulk_logged.. If the transaction log contains any bulk
logged transactions, you won't be able to do a point-in-time restore.
--
Jacco Schalkwijk
SQL Server MVP
"steve harris" <anonymous@.discussions.microsoft.com> wrote in message
news:968EB502-2357-4B76-A8AF-DE5839BFC9CE@.microsoft.com...
> Do I need to re-cycle the server when changing the recovery model from
full to bulk logged? I can't find anything in the newsgroup or in the books
online.

Changing Recovery Mode

If I change recovery mode on a database from FULL to SIMPLE, when is this
change effective?Immediately.
--
Andrew J. Kelly SQL MVP
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>|||Hi,
Change will be effective immediately. But if you need to change the Recovery
model back to FULL then you need to do a FULL database
backup to recreate the backup chain. After that you could start your
Transaction log backup.
Thanks
Hari
MCDBA
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>

Changing Recovery Mode

If I change recovery mode on a database from FULL to SIMPLE, when is this
change effective?
Immediately.
Andrew J. Kelly SQL MVP
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>
|||Hi,
Change will be effective immediately. But if you need to change the Recovery
model back to FULL then you need to do a FULL database
backup to recreate the backup chain. After that you could start your
Transaction log backup.
Thanks
Hari
MCDBA
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>