Backup of database

Question

Backup of database

sanjay s jadhav

SSC Rookie

Points: 39
More actions
November 26, 2006 at 11:46 pm

#115185

Hi
How to take the backup of a database without a a LOG by a sql script command.
I dont want to maintain a Log file in my backup
Regards,

sanjay

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

You probably need to read more about how SQL Server works. There is always a log file with your database and you need to back it up or truncate it.

A backup of the database will not include the log.

In books online, look up:

BACKUP DATABASE

BACKUP TRANSACTION

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 2

You can not avoid the log from being backed up, however, its amount can be varied. If you take the Full DB backup, then SQL Server will also backup log but just enough for the restoration of the backup set. I think you can take the full backup of the database. It should server your need.

Godd luck.

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

MohammedU SSCoach Points: 19644 More actions · Answer 3

If your db recovery model is full your full backup will backup the tlog but it will not truncate the log which may cuase disk to run of space...

If you don't want point time recovery you change your recovery model to simple...and run the command...

Backup database <dbname> to disk = 'backup location with file name.'

See Backup chapter in BOL for details..

MohammedU
Microsoft SQL Server MVP

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

For databases in simple recovery mode use:

BACKUP LOG database_name WITH TRUNCATE_ONLY

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Susan S SSCommitted Points: 1771 More actions · Answer 5

I have the similar problems the log grows...

so I create a job to shrink the log file and run once a week.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

Susan,

See Mohammed's post above. If you don't need point-in-time recovery ability and you don't want to do log backups, change the database recovery model to "Simple" and forget about truncating and shrinking the log file.

Greg

MohammedU SSCoach Points: 19644 More actions · Answer 7

Susan,

Shrink command will not truncate log, only free up allocated free space to OS.

MohammedU
Microsoft SQL Server MVP

Shane Miller SSC Eights! Points: 827 More actions · Answer 8

If it is a production database, I would recommend the full database recovery model with frequent backups of the transaction log if it is normally large. Also, remember that if you periodically run the backup log... with truncate_only and/or the dbcc shrinkfile command (to reclaim space), you need to perform a full backup afterwards. Since both the truncate and shrinkfile commands are not logged, they will create a discrepancy in your transaction log sequence, thereby causing you to lose your ability to recover to point-in-time.