SQLServer 2000 recovery models

Hi List,

Anyone using SQLServer 2000 - what recovery model do you use (simple, or
full) ?

Further, does anyone use Veritas backup exec software to secure SQLServer
2000 database ?

We use backup exec and it will only backup databases that are set to be a
'full' recovery model but thats OK because we should be using that anyway.
However we have tried a restore test and were unable to restore this
database. The error we get is that the 'tail of the database transaction log
has not been backed up' so as it suggests we take a another backup of the
database but this time just a 'transaction log' backup - but after this we
still get the same error when we try to restore the database.

I am sure this is our approach that is wrong, anyone got any ideas ?

Xe SP16 Win2K, SQL2K


Andy Smith
Technical Consultant

WHITEHOUSE
Consultants

http://www.whitehouse-consult.co.uk

Office: 01564-711037
Mobile: 07949 603770





Andy Smith
Whitehouse Consultants
Win2K SQLServer7 Xe

Actually you bring up a good point that I am not going
to address. I am going to slightly change the topic
and advise SQL2000 admins to change the recovery model
to Bulk Logged when doing Data Conversion/Loads, Mass
Production Cut-Overs, etc.

Forgot to set it before you started that R98403?
Don't worry since the setting can be changed
'on-the-fly'.

You may now have your topic back.



--- andy_smith <[email protected]>
wrote:
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=OW&Number=29659


__________________________________________________

Andy :

Simple model may be used as long as no BLOB/Image/Unlimited text fields
are used (all columns whose transactions are not logged into MSSQL
Transaction Log).
In that case, I would never backup CO databases (JDE_xx7333) and
JDE7333 databases according to a simple model. These databases should be
backed up according to full recovery model.
Business Data and Control Tables have a few cases of non logged fields,
but they're not very significative. In a few words, fits quite well
on the simple recovery schema.
However, if you want to be absolutely sure that you'll lost anything
at all, then proceed with a full recovery backup strategy on
every database.

Sebastian Sajaroff

A "simple" recovery model means that once a transaction has been committed to the database, it is flushed from the trans. log. "full" recovery means that all transactions are saved in the log, until it is backed up, at which time the transaction log will be truncated.

For all my databases other than production databases I use a simple recovery model cause generally I'm not fussed about keeping backups of the trans. logs, a once a day backup is fine. For production databases I have a maintenance plan that does backups of my trans. logs every 30 Mins, the benefit of this approach is that I can restore my prod. databases to within 30 mins of something going wrong, compared to having to go to yesterdays backup. Ideally for performance and fault tolerance you databases, trans. logs and trans. log backups should all be on different disks.

When you do the restore there should be a "recovery" tick box, basically this indicates that you do not want to apply any other transaction logs after restoring the db, and it will leave the database in a usable state.

Hope this helps.

Phil Anderson.
[email protected]
W2K/SQL2K/MSCS
Xe SP18/Update4

I think when we discussed this last, most people seem to use SQL tools to do the SQL backup and then 3rd party tools to backup the backup files! It is much cleaner this way since SQL then has a complete record of what was done, when and how often! Then there is no need buy 3rd party Open file backup utilities. SQL backups seem to be very reliable. Recovery is easier too.

OneWorld Xe Sp16_018, W2k, W2K TSE, SQL 2K on Compaq Proliant.<P ID="edit"><FONT SIZE=-1>Edited by antipodes1 on 2/28/02 07:24 AM.</FONT></P>

I agree with that... There's one thing you should be aware of, and it's not
to use the same tape for several!! times. I had a problem some time ago
where the tape we used was more than 6 month old (imagine how many times it
was played and rewinded)and when we needed to restore some info from it...
surprise the tape was damaged and all on it was lost...

Just my poor and sad contribution...

OW B73.3.2 SP 16.1 / NT 4.0 Sp 6a/ SQL 7.0 (XE and Win2000 soon)

Greetings.
S.E. Ricardo Paz Castanon
Systems Chief
Comercial de Herramientas S.a de C.v.
+52(81) 8369-35-35 ext. 286
[email protected]
www.cohesa.com