SQL server backup and truncate log file

alwinkotiya

Hi all,

If I set recovery model SIMPLE then truncation of log files will be
automated , but I will lose
database changes made since the last backup was taken.
Is there any way to automate truncation of log file and
backup of database upto last change has been done????

Plz Help.

Jun 13 '06 #1

Subscribe Reply

11834

Robert Klemme

al*********@gma il.com wrote:

Hi all,

If I set recovery model SIMPLE then truncation of log files will be
automated , but I will lose
database changes made since the last backup was taken.
Is there any way to automate truncation of log file and
backup of database upto last change has been done????

Maintenance plan.

robert

Jun 13 '06 #2

alwinkotiya

I did not get you. Please specify.

Robert Klemme wrote:

al*********@gma il.com wrote:
Hi all,

If I set recovery model SIMPLE then truncation of log files will be
automated , but I will lose
database changes made since the last backup was taken.
Is there any way to automate truncation of log file and
backup of database upto last change has been done????

Maintenance plan.

robert

Jun 13 '06 #3

Erland Sommarskog

(al*********@gm ail.com) writes:

If I set recovery model SIMPLE then truncation of log files will be
automated , but I will lose
database changes made since the last backup was taken.
Is there any way to automate truncation of log file and
backup of database upto last change has been done????

Wait here. You don't want to truncate the log regularly. Then you can
just as well run with simple recovery model. What you want to do is
to *back up* the transaction log regularly.

The best way to do this is of course to do it from SQL Server Agent. You
can use the interactive dialog in for backup in Enterprise Manager/Mgmt
Studio and schedule the job from these dialogs.

Provided, that is, you are not running SQL 2005 Express which does not
include Agent.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 13 '06 #4

Similar topics

2912

Log file automaticaly shrink during backup log without particular option

by: rjp | last post by:

Hi, I am using a SQLServer 7 SP4 version. I have scheduled one job with the SQLServer agent which backup the log of the databases every day. The command is : "backup log base to with noinit" At the end of the command, the logfile shrink from 20Mo to 1Mo. I don't understand because I though that only 'backup log with

Microsoft SQL Server

9594

Transaction log size after backup.

by: D. Buck | last post by:

I have a MS SQL 2000 dB that is 3GB, the transaction log is about 2GB. I do a full dB backup every night. My question is, shouldn't the transaction log shrink down to next to nothing after a full dB backup? D. p8oust7eh+

Microsoft SQL Server

7564

Transaction Logging Backup Set

by: Thiko | last post by:

Hi I take one nightly full database backup at 02:00 and backup the transaction log to one backup set every 15mins. The commands to do this are as follows and are set up to run as database jobs: -- Database Backup BACKUP DATABASE TO WITH NOINIT ,

Microsoft SQL Server

1713

Does the transaction log on a publisher SQL server get truncated when the database is backed up, but the distributor is down?

by: mokles | last post by:

Hello All, The setting is SQL server 7, on Windows NT. If the distributor can not access the log on the publisher database ( but subscribers are not deleted yet), will the full backup of the publisher database truncate the transaction log? My guess is, it will not truncate the log. Can any one confirm please? Thanks,

Microsoft SQL Server

4863

dbcc shrinkfile - SQL Server 2000 - invalidate log?

by: deebeeay | last post by:

Hi, I need to shrink a database file and was wondering whether it is required to run a full backup after the shrink operation. In SQL Server 7.0 shrinkfile was a non-logged operation so would invalidate your transaction logs. Is the same true for 2000? Obviously as a matter of course I would backup before and after the operation but going forward I may want to implement this on a regular

Microsoft SQL Server

18227

Does a Full Backup include transaction logs

by: Brian D | last post by:

In MS SQL 2005 when you do a Full Backup does it also backup and truncate the transaction logs or do I need to back the transaction logs up separately? Thanks. Brian

Microsoft SQL Server

5112

SQL 6.5 server connection problem

by: sherifbk | last post by:

Problem description ============== - I have 4 clients and 1 server (SQL server) - 3 clients are Monitoring console 1 client is operation console - Monitoring console collects some data from the control unit and store them into the Sql server - The operation console then retrieve this data from the sql for reporting and statistics purposes - I am using ODBC connection - The problem is that the operation console is not able to...

Microsoft SQL Server

3505

How do I make a backup device file smaller ?

by: David Greenberg | last post by:

Hi I have a backup device file ... ".bck" whick has grown pretty large. Is there any way I can reduce its size ? Thanks David Greenberg

Microsoft SQL Server

3483

does this backup sequence commit all data to the database

by: Roger | last post by:

backup log testdb with truncate_only DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS backup database testdb to disk = '\\DC01\Backups\DB01\testdb.bak' with init and does the shrinkfile command reduce the size of the ldf ?

Microsoft SQL Server

7991

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8395

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8050

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8265

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5850

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5438

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

3939

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2412

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1504

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP