Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How can I set the database recovery model?

5 posts in General Discussion Last posting was on 2012-11-15 15:03:23.0Z
Galen Henderson Posted on 2012-11-15 13:30:06.0Z
Message-ID: <8d575e15f79c8cf911a2b416963@forums.sybase.com>
From: Galen Henderson <pcgalen@yahoo.com>
Subject: How can I set the database recovery model?
Newsgroups: sybase.public.ase.general
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Nov 2012 05:30:06 -0800
X-Trace: forums-1-dub 1352986206 172.20.134.152 (15 Nov 2012 05:30:06 -0800)
X-Original-Trace: 15 Nov 2012 05:30:06 -0800, vip152.sybase.com
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31490
Article PK: 74379

Hello all. I know this is a simple question but I have googled and can't
find the answer. How can I set the recovery model for a database? I have
looked at the stored procedures and the create/alter database syntax and
it is not listed. Any help appreciated!

WBR,
Galen Henderson


"Mark A. Parsons" <iron_horse Posted on 2012-11-15 14:20:23.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How can I set the database recovery model?
References: <8d575e15f79c8cf911a2b416963@forums.sybase.com>
In-Reply-To: <8d575e15f79c8cf911a2b416963@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121031-1, 10/31/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50a4fa27$1@forums-1-dub>
Date: 15 Nov 2012 06:20:23 -0800
X-Trace: forums-1-dub 1352989223 172.20.134.152 (15 Nov 2012 06:20:23 -0800)
X-Original-Trace: 15 Nov 2012 06:20:23 -0800, vip152.sybase.com
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31491
Article PK: 74378

What exactly are you looking for?

- recovery order? (sp_dbrecovery_order)
- database durability? (create/alter database ... durability)
- something else? (what is your ASE version? please provide example of what you're looking for)

On 11/15/2012 06:30, Galen Henderson wrote:
> Hello all. I know this is a simple question but I have googled and can't find the answer. How can I set the recovery
> model for a database? I have looked at the stored procedures and the create/alter database syntax and it is not listed.
> Any help appreciated!
>
> WBR,
> Galen Henderson
>
>


Luc Van der Veurst Posted on 2012-11-15 14:26:00.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <8d575e15f79c8cf911a2b416963@forums.sybase.com>
Subject: Re: How can I set the database recovery model?
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646: Format=Flowed; Response
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50a4fb78$1@forums-1-dub>
Date: 15 Nov 2012 06:26:00 -0800
X-Trace: forums-1-dub 1352989560 172.20.134.152 (15 Nov 2012 06:26:00 -0800)
X-Original-Trace: 15 Nov 2012 06:26:00 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31492
Article PK: 74380


"Galen Henderson" <pcgalen@yahoo.com> wrote in message
news:8d575e15f79c8cf911a2b416963@forums.sybase.com...
> Hello all. I know this is a simple question but I have googled and can't
> find the answer. How can I set the recovery model for a database? I have
> looked at the stored procedures and the create/alter database syntax and
> it is not listed. Any help appreciated!

If you don't want to make (and keep) transaction log dumps, then you should
set the 'trunc log on chkpt' option for that database :

use master
go
sp_dboption <dbname>, 'trunc log', true
go

That's what you get when you choose the simple recovery model in MS SQL.
You then need to take database dumps. You can recover from these database
dumps, so all transactions between the latest dump and the time of recovery
will be lost.

If you set the database option to false (default) :

use master
go
sp_dboption <dbname>, 'trunc log', false
go

then you need to schedule dump database actions and dump transaction
actions.
You can then recover the database unto the most recently taken transaction
log dump.
The period you can accept dataloss will determine the frequency of the
transaction
log dumps.

Hth,
Luc.


Galen Henderson Posted on 2012-11-15 15:03:23.0Z
Message-ID: <8d575e15f7b28cf912733234bc6@forums.sybase.com>
From: Galen Henderson <pcgalen@yahoo.com>
Subject: Re: How can I set the database recovery model?
Newsgroups: sybase.public.ase.general
References: <50a4fb78$1@forums-1-dub>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Nov 2012 07:03:23 -0800
X-Trace: forums-1-dub 1352991803 172.20.134.152 (15 Nov 2012 07:03:23 -0800)
X-Original-Trace: 15 Nov 2012 07:03:23 -0800, vip152.sybase.com
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31495
Article PK: 74384

Hello Luc Van der Veurst,
Thanks for your reply. Microsoft's gui makes it so easy to set opions like
this. I am a newbie to Sybase and appreciate all of your help. Thanks.

WBR,
Galen Henderson
mailto:pcgalen@yahoo.com

> "Galen Henderson" <pcgalen@yahoo.com> wrote in message
> news:8d575e15f79c8cf911a2b416963@forums.sybase.com...
>
>> Hello all. I know this is a simple question but I have googled and
>> can't find the answer. How can I set the recovery model for a
>> database? I have looked at the stored procedures and the
>> create/alter database syntax and it is not listed. Any help
>> appreciated!
>>
> If you don't want to make (and keep) transaction log dumps, then you
> should set the 'trunc log on chkpt' option for that database :
>
> use master
> go
> sp_dboption <dbname>, 'trunc log', true
> go
> That's what you get when you choose the simple recovery model in MS
> SQL. You then need to take database dumps. You can recover from these
> database dumps, so all transactions between the latest dump and the
> time of recovery will be lost.
>
> If you set the database option to false (default) :
>
> use master
> go
> sp_dboption <dbname>, 'trunc log', false
> go
> then you need to schedule dump database actions and dump transaction
> actions.
> You can then recover the database unto the most recently taken
> transaction
> log dump.
> The period you can accept dataloss will determine the frequency of the
> transaction
> log dumps.
> Hth,
> Luc.


recoverymodel Posted on 2012-11-15 14:54:19.0Z
Sender: 565a.50a4ffd2.1804289383@sybase.com
From: recoverymodel
Newsgroups: sybase.public.ase.general
Subject: Re: How can I set the database recovery model?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50a5021b.5712.1681692777@sybase.com>
References: <8d575e15f79c8cf911a2b416963@forums.sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 15 Nov 2012 06:54:19 -0800
X-Trace: forums-1-dub 1352991259 172.20.134.41 (15 Nov 2012 06:54:19 -0800)
X-Original-Trace: 15 Nov 2012 06:54:19 -0800, 172.20.134.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31494
Article PK: 74383

This term is not vaid with sybase. Its Microsoft SQL server
terminology.

e.g.
MSSQLSERVER: recovery model = SIMPLE is equivalent of
setting 'trunc log on chkpt' option on database

MSSQLSERVER: recovery model = FULL is equivalent of not
setting 'trunc log on chkpt' and 'select into' options of
database.

simple way would be to understand what SQL Serve recovery
model does, then you wouldnt have issue trying to emulate
the same in Sybase!

> Hello all. I know this is a simple question but I have
> googled and can't find the answer. How can I set the
> recovery model for a database? I have looked at the
> stored procedures and the create/alter database syntax and
> it is not listed. Any help appreciated!
>
> WBR,
> Galen Henderson
>
>