T-Log : Recovery Model

It will not be exactly the same as either in all cases, but SIMPLE will be very close to BULK. -- Peter Byrne Microsoft SQL Server Storage Engine This posting is provided "AS IS" with no warranties, and confers no rights. wrote in message news:cd6210eb-d7bf-40d7-9e4b-4594b3cd29e9@discussions.microsoft.com...
> Does Transaction log in Simple recovery model behave like Full during
> the transaction or it behaves like Bulk Logged ? >
> To make my Q simple, if I do BULK operation (BULK INSERT) & my DB is in
> simple recovery model, does T-Log records BUlk Operation OR it does it
> log FULL until the BULK operation is complete. > > >
>

MVPUser at 2007-10-8 >

In SQL 2005, when T-logs are restored in Log Shipping, is the destinatin DB available, or it goes to Loading state as in SQL 2000 ?

Hi,

sure:

Exploring the Recovery Models SQL Server 2000 has three models: simple, full, and bulk-logged. Let's look at what each model means, starting with the easiest. Simple: The simple model describes just that: the simplest and easiest situation to manage. When a database is set to this model, there is no way you can back up only the changes made since the last backup. Instead, only full backups are allowed. One benefit of this model is that the transaction log won't become full from transactions occurring between the full backups. Whenever the database performs a checkpoint, space in the log is reclaimed. Additionally, non-logged operations such as bulk copy are permitted. Full: The full model allows you to create not only complete backups of the database, but also incremental backups of only the changes that have occurred since the last full backup. An added benefit is that it's possible to recover the database to a particular point in time. For example, if a user accidentally deletes all accounts in a database at 1 PM, it's possible to restore the database up to 12:59 PM, right before the deletion of the accounts occurred. Under this mode, space in the transaction log is only reclaimed when a backup of the transaction log is made. When this occurs, all the changes stored in the transaction log are written to the backup and the space is freed up. Therefore, databases in this mode need to have enough space available for the transaction log to store all the transactions that occur between each backup. Additionally, non-logged operations are not allowed. Bulk-Logged: The bulk-logged model lies between the other two models. On the one hand, incremental backups of the database are possible. The transaction log is treated the same way in this model as in the full model. However, bulk copy operations are only minimally logged. Instead of logging each insert into the table, SQL Server only logs the minimum necessary to recover the data if the backup is needed. However, because of this, if a bulk copy operation occurs, point-in-time recovery (described in the previous paragraph) is not possible. Recovery can only stop at the end of a transaction log.

(from: http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Is the DB online in SQL 2005 while a log is restored using standBy mode ?