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