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 security model

how do i reconfigure an installed sql server to use standard security
(sql login/pwd) instead of only accepting trusted connections
(requiring windows login/pwd)
thanks,
jasonnever mind, i found it in the enterprise manager server properties
security tab.

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 Licensing Model on production server

Hello All,
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!
Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:

> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> to change the licensing model on the server to reflect the proper licensing
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!
|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that value.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something the
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:

Changing Licensing Model on production server

Hello All,
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> to change the licensing model on the server to reflect the proper licensing
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that value.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something the
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
> > Hello All,
> >
> > I have a pre-existing SQL server that appears to have been installed with
> > the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> > to change the licensing model on the server to reflect the proper licensing
> > configuration that is in place.
> >
> > Is there a way that this can ben done absent of having to reinstall SQL?
> >
> > Thanks!

Changing Licensing Model on production server

Hello All,
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I nee
d
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:

> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I n
eed
> to change the licensing model on the server to reflect the proper licensin
g
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that valu
e.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something th
e
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
>

changing dimensions

Hi,
how to model changes (I've read R.Kimball) in dimensions and (more
important) how to implement them in OLAP e.g. MS AS ?
I read some papers from scientists but they are not based on commercial
solutions - I don't know how to show changes in MOLAP and how to solve
the problem.
can you give me any suggestions, links etc. ?
thx in advance
Joel"Joel" <dmrozek99@.poczta.onet.pl> wrote in message
news:4005442F.3060307@.poczta.onet.pl...

quote:

---

> Hi,
> how to model changes (I've read R.Kimball) in dimensions and (more
> important) how to implement them in OLAP e.g. MS AS ?
> I read some papers from scientists but they are not based on commercial
> solutions - I don't know how to show changes in MOLAP and how to solve
> the problem.
> can you give me any suggestions, links etc. ?
>

---

A little background for you - I am currently in the process of developing a
prototype for a Kimball-based data warehouse. I have experienced your
challenges but found Kimball's methodology very practical and beneficial.
To answer your questions:
1. Links: Your best bet would be www.rkimball.com, select the articles and
Ralph's Classic Favorites. You should also buy his book "Data Warehouse
Lifecycle Toolkit".
2. Modeling changes in dimensions: this is a very important topic. Once
again, this is addressed in Kimball's articles on slowly changing
dimensions.
For more specific answers, please provide specific questions.
HTH

Changing Db Model

Hi
What are the steps EXACTLY in order to change a database model from bulk
insert to Full or vice/versa? ... so that it is immediately becomes
effective i.e. Stopping/starting service? Do you have to delete or backup
the old transaction log first along?
Could this be done without any downtime? If so, how?
Many thanks
ShahriarYou can just switch the database recovery mode from full to bulk-logged
without doing anything else. However, if you switch from full to simple, yo
u
can't do transaction log backup anymore. You don't need to stop any service
s
or delete any backups.
The drawback is you can't do point in time recovery if the recovery mode is
not full.
"Shahriar" wrote:

> Hi
> What are the steps EXACTLY in order to change a database model from bulk
> insert to Full or vice/versa? ... so that it is immediately becomes
> effective i.e. Stopping/starting service? Do you have to delete or backup
> the old transaction log first along?
> Could this be done without any downtime? If so, how?
> Many thanks
> Shahriar
>
>
>|||First, did you read through the relevant topics in SQL Server Books Online?
Stopping & restarting of the service is not required for changing the
recovery models
No, unless you want to truncate the log manually when you move from higher
recovery models to simple mode.
Yes.
Using ALTER DATABASE statement, with recovery_options set to RECOVERY FULL,
BULK_LOGGED or SIMPLE. For exact syntax refer to SQL Server Books Online.
Alternatively, you can use the EM interface to do this as well.
Anith|||Jack
Thank you for your response. Could you please explain the following
behaviour?
1. Created a database called testdb
2. Created a table called car with the following 2 fields
1) ID Int Identity
2) Car Char[10]
3. Created a back up set called X from management
4. Changed Database model from full to bulk/insert for testdb database
5. Backup up the database testdb (eventhough the table car has no values)
Required so that transactions need a preliminary backup.
6. In query Analyzer executed the following query
5. in query analyzer
Declare @.i int
set @.i=0
while @.i<=6000
begin
insert into car (car) values ('trans'+str(@.i,5))
set @.i=@.i+1
end
6. Switched to enterprise and after a refresh, noticed the transaction log
increased
dramatically.
I am under the impression that with a database model of bulk/insert the
above insert statement will not be logged.
FYI, I did have a Database that was set for bulk/insert and switched it to
full to record transactions. I noticed that it didnt take effect until I ha
d
to refresh the service !
Again, thank you for taking time trying to help me.
Shahriar
"Jack" wrote:
> You can just switch the database recovery mode from full to bulk-logged
> without doing anything else. However, if you switch from full to simple,
you
> can't do transaction log backup anymore. You don't need to stop any servi
ces
> or delete any backups.
> The drawback is you can't do point in time recovery if the recovery mode i
s
> not full.
> "Shahriar" wrote:
>|||Anith
Thank you for your response. Please take a look at my reply to Jack.
Thanks again
Shahriar
"Anith Sen" wrote:

> First, did you read through the relevant topics in SQL Server Books Online
?
>
> Stopping & restarting of the service is not required for changing the
> recovery models
>
> No, unless you want to truncate the log manually when you move from higher
> recovery models to simple mode.
>
> Yes.
>
> Using ALTER DATABASE statement, with recovery_options set to RECOVERY FULL
,
> BULK_LOGGED or SIMPLE. For exact syntax refer to SQL Server Books Online.
> Alternatively, you can use the EM interface to do this as well.
> --
> Anith
>
>|||>6. Switched to enterprise and after a refresh, noticed the transaction log
> increased
> dramatically.
> I am under the impression that with a database model of bulk/insert the
> above insert statement will not be logged.
Please get a grip here. Please review BOL:
When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX,
and bulk loading data operations is minimal and therefore requires less log
space. In exchange for better performance and less log space usage, the risk
of exposure to loss is greater than with full recovery.
does not say that an insert statement is not logged!
dlr
"Shahriar" <Shahriar@.discussions.microsoft.com> wrote in message
news:E59505B3-B848-4C15-A2D2-E8F73BAE232F@.microsoft.com...
> Jack
> Thank you for your response. Could you please explain the following
> behaviour?
> 1. Created a database called testdb
> 2. Created a table called car with the following 2 fields
> 1) ID Int Identity
> 2) Car Char[10]
> 3. Created a back up set called X from management
> 4. Changed Database model from full to bulk/insert for testdb database
> 5. Backup up the database testdb (eventhough the table car has no values)
> Required so that transactions need a preliminary backup.
> 6. In query Analyzer executed the following query
> 5. in query analyzer
> Declare @.i int
> set @.i=0
> while @.i<=6000
> begin
> insert into car (car) values ('trans'+str(@.i,5))
> set @.i=@.i+1
> end
> 6. Switched to enterprise and after a refresh, noticed the transaction log
> increased
> dramatically.
> I am under the impression that with a database model of bulk/insert the
> above insert statement will not be logged.
> FYI, I did have a Database that was set for bulk/insert and switched it
to
> full to record transactions. I noticed that it didnt take effect until I
had
> to refresh the service !
> Again, thank you for taking time trying to help me.
> Shahriar
> "Jack" wrote:
>
simple, you
services
is
bulk
backup|||Shahriar,
If you set the database recovery mode to Bulk-Logged, the activities won't
be logged are Bulk Insert, Select Into, Create Index, and the text operation
s
(Updatetext...). Insert operations will still be logged and that's why you
see the transaction log grows in your example. I have not encountered the
change of the database recovery mode only takes effect after services restar
t
yet, so I can't comment on that.
Seems like your main concern is the size of the transaction log. There are
other ways to control it besides changing the recovery mode. There are many
discussions on that topic in both this forum and .server forum already.
"Shahriar" wrote:
> Jack
> Thank you for your response. Could you please explain the following
> behaviour?
> 1. Created a database called testdb
> 2. Created a table called car with the following 2 fields
> 1) ID Int Identity
> 2) Car Char[10]
> 3. Created a back up set called X from management
> 4. Changed Database model from full to bulk/insert for testdb database
> 5. Backup up the database testdb (eventhough the table car has no values)
> Required so that transactions need a preliminary backup.
> 6. In query Analyzer executed the following query
> 5. in query analyzer
> Declare @.i int
> set @.i=0
> while @.i<=6000
> begin
> insert into car (car) values ('trans'+str(@.i,5))
> set @.i=@.i+1
> end
> 6. Switched to enterprise and after a refresh, noticed the transaction log
> increased
> dramatically.
> I am under the impression that with a database model of bulk/insert the
> above insert statement will not be logged.
> FYI, I did have a Database that was set for bulk/insert and switched it t
o
> full to record transactions. I noticed that it didnt take effect until I
had
> to refresh the service !
> Again, thank you for taking time trying to help me.
> Shahriar
> "Jack" wrote:
>|||A couple issues:
1. This is a bit off mark. Recovery models are not about logging or not
logging per se. It is simply a mechanism to adjust your data loss exposure
based on your frequency of data and/or log backups. All DML activities are
logged in all recovery models to some extent. However Bulk logged recovery
provides optimal performance and resource usage for large bulk copy
operations.
2. The repro you posted does not illustrate a bulk insert operation. The
operations which are of interest in Bulk-logged recovery models are BCP,
BULK INSERT, WRITETEXT, UPDATETEXT, SELECT INTO, CREATE INDEX etc. A regular
INSERT statement generally is not considered as a bulk load operation.
Finally, the behavior you have seems consistent. To reduce the size of the
log, you can do a BACKUP LOG with NO_LOG option. To reduce the physical file
size, simply issue a DBCC SHRINKFILE statement.
Anith|||On Thu, 7 Apr 2005 09:46:23 -0500, Anith Sen wrote:
(snip)
>Using ALTER DATABASE statement, with recovery_options set to RECOVERY FULL,
>BULK_LOGGED or SIMPLE. For exact syntax refer to SQL Server Books Online.
>Alternatively, you can use the EM interface to do this as well.
Hi Anith,
I seem to recall that, in order for a change from simple to either
bulk-logged or full to become effective, a full database backup has to
be made after the ALTER TABLE statement.
Am I mistaken?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I seem to recall that, in order for a change from simple to either
I did not know that, Hugo. Can you post some details or the source of this
information?
Anith