Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
franco
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-04-23 : 05:04:21
|
SQL 7.0 SP1 on NT 4 SP6a Cluster.We have a DB with the option "select into/bulk copy" set to true.When non-logged operations happens, the backup of the transaction log fail, so we do a differential backup instead and everything is ok.My questions are:UPGRADING to SQL 2K,1 Do we have to choose the "Bulk-Logged" recovery model?and if so,2 Do we still need to run differential backup because backup of t-log will fail as on SQL 7.0?Can somebody help me with this?TIA.Franco |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-04-23 : 08:54:13
|
quote:
UPGRADING to SQL 2K,1 Do we have to choose the "Bulk-Logged" recovery model?and if so,
Yes, if you want your bulk operations (such as CREATE INDEX and BCP) to be minimally logged. Minimal logging means that SQL Server does not record all the bulk data movement in the transaction log, but instead simply notes which pages were affected. As a result, your transaction log does not grow much, and the performance is high.quote:
2 Do we still need to run differential backup because backup of t-log will fail as on SQL 7.0?
Log backup will not fail. Whenever you run log backup on a database that is in BULK_LOGGED recovery mode, in addition to the recorded transactions SQL Server will write out all the data from the pages affected by bulk operation (so the size of the backup device might be much larger than usual)... Using this log backup, you will be able to recover the database to a point in time before or after the bulk operation took place... It will not allow you to recover to a point in time during the bulk operation. If your database is in FULL recovery mode, all bulk operations are fully logged (which means your log will be huge, and bulk operations will be slow), but you always will be able to recover to any point in time using your transactional log backups. There is more info on this in BOL---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested.Edited by - izaltsman on 04/23/2002 09:00:24Edited by - izaltsman on 04/23/2002 09:01:29 |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-04-23 : 09:05:58
|
| izaltsman, thank you very much!Best Regards.Franco |
|
|
|
|
|
|
|
|
non-logged operations