1

I have a database "DBName" on SQL Server 2008. I want to take backup of it without logs(.ldf file). Because this log file is around 20 GB and I don't want to increase the size of backup file.

I also want to do this without truncating logs from current Live database.

Meaning, the backed up copy shouldn't contain transaction logs but the live database "DBName" should remain unaffected.

P.S. - I am taking backup through following script. Variables are set from UI in WPF.

exec('BACKUP DATABASE '+ @DBName +' TO DISK ='''+ @DBBackupPath +'''')

Thank you.

EDIT

Should SQL Server Simple Recovery Model help ?

Arjun
  • 431
  • 2
  • 8
  • 21

1 Answers1

1

A full backup only contains the portion of the log that was generated during the backup. Should be very small.

If you enable simple recovery that will throw away all logs that are not backed up and break the log chain. Is there a reason to be in full recovery mode? If yes you should probably make yourself more familiar with how to not break the log chain.

usr
  • 168,620
  • 35
  • 240
  • 369
  • If a full backup contains small size of log, then why is it that when we restore from such back up, log file(.ldf) is around 18 to 20GB? – Arjun Jan 07 '15 at 09:14
  • I tried to take backup of DB with simple recovery model. And restored using same to new Database. Backup file was 32.9GB After restoring MDF file --> 35.4GB LDF file --> 18GB I don't understand the maths. Help? – Arjun Jan 07 '15 at 09:22
  • That LDF surely was almost empty. – usr Jan 07 '15 at 09:24
  • then why such huge size? – Arjun Jan 07 '15 at 09:29
  • The LDF retains the size it has when it was backed up. There might be an implementation reason for that. Or the dev team just thought it's best to keep the size that you apparently want. – usr Jan 07 '15 at 09:31
  • Is it possible that back up file has MDF and LDF files in compressed form? – Arjun Jan 07 '15 at 09:37
  • your answers, helped me google things which in turn answered my questions. [This helped](http://dba.stackexchange.com/questions/3694/why-is-a-bak-so-much-smaller-than-the-database-its-a-backup-of) Thank you. – Arjun Jan 07 '15 at 09:44