SQL 2008 log file backup

Question

SQL 2008 log file backup

marees.inspire

Ten Centuries

Points: 1324
More actions
March 26, 2009 at 4:25 am

#93300

I am unable to take Log file backup using
Backup log Test with Truncate_only
in SQL 2008.
Please help me on this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

The truncate_only operation has been deprecated in SQL Server 2008. There is no replacement. The log is automatically truncated when the db is in simple recovery. There are more details in Books Online.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

marees.inspire Ten Centuries Points: 1324 More actions · Answer 2

marees.inspire

Ten Centuries

Points: 1324

March 26, 2009 at 5:38 am

#966540

Thanks for the help

EdVassie SSC Guru Points: 60478 More actions · Answer 3

Please read what Books Online says about Transaction Logs, and what a transaction log backup means.

The command you want to do does not take a backup of the transaction log. What it does do is discard the transaction log data.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

marees.inspire (3/26/2009)
Backup log Test with Truncate_only

and Why do you want to do this anyway:-P? That's a disaster.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

We used the truncate_only functionality for development and test databases. For some load tests we take production databases to our test system. Our test environment is not as large as the production. The data within this databases are completely unimportant. After those tests the databases have been deleted. To safe disk space this was quiet helpful.

Sure in a production environment this is no option.

Greets

Flo

kavi_jaya Newbie Points: 9 More actions · Answer 6

Hi,

I have multiple SQL 2008 production environment, and also Mirroring / Log Shipping enabled. Hence, changing the database to "simple" recovery mode is not possible. Hence, I wrote this code,

-- Replace the "DatabaseName" with Name of the Database

Use DatabaseName

go

SP_helpDB DatabaseName

Go

---Get the Log FileName and replace "LogFileName"

DBCC ShrinkFile(LogFileName,1)

Go

Backup Log DatabaseName with Truncate_Only

Go

DBCC ShrinkFile(LogFileName,1)

Go

Backup Log DatabaseName with Truncate_Only

Go

DBCC ShrinkFile(LogFileName,1)

Go

Kavitha Jayaprakash