0

Log files are often large an unwieldy. Is there a way to "backup" a SQL Server database without any log files (or with empty log files)?

Ben Aston
  • 53,718
  • 65
  • 205
  • 331
  • I've wondered this as well. If I take a full database backup, then the log files up until the point of backup become irrelevant, since I can now restore to the last backup. If you truncate, you lose point-in-time before that, but if that's fine, is there a problem with truncating after backup? – SqlRyan Jan 12 '09 at 19:23

3 Answers3

1

Portions of the log are actually included in the backup. It's how you get consistency on a restore. The pages that change while the backup is running are grabbed from the log( or the logged changes) and then those are applied at the end of the restore process.

You can't get around that, but you can limit the amount of logging SQL Server does. You do this by putting a database in simple mode, but if you do this, understand that you can't recover then up to the time when someone issued at

delete myTable

and forgot the WHERE clause. You can only recover from your full backups.

What you want to do is back up your database (with the BACKUP DATABASE command) regularly, and also run BACKUP LOG commands more often. Typically people to a full backup every night of small databases and then log backup every hour. This allows you to recover to any point during the day, assuming you keep all these backup files.

It also manages the log file sizes since the BACKUP LOG command frees space in the log files for reuse.

Steve Jones
  • 148
  • 1
  • 4
0

Just before you take the full database backup, execute:

BACKUP LOG databasename WITH TRUNCATE_ONLY

The reason to do it before is so that a future log backup will still preserve point-in-time consistency. This way, you'll always have a valid chain (from this full backup on, anyway - the TRUNCATE will prevent you from doing a point-in-time restore since whenever your last log backup was, though all your data-only backups will still be valid).

SqlRyan
  • 33,116
  • 33
  • 114
  • 199
0

Sorry, but your question makes no sense.

When you BACKUP DATABASE, you backup data pages from the data files (MDF and NDFs) ,not the file(s). The engine then appends any changes (from the log entries) that happened during the backup.

The log entries (not files) are backed up with BACKUP LOG. This is not needed if the database has the simple recovery model.

When you restore the DB, then it recreates the files on disk. Is this what you mean?

The log file itself is absolutely essential to how all RDBMSes work.

The only question here is how you backup/restore them in the context of BCP/DR strategy.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Hi gbn, thanks for your input. Yeah my detailed understanding is pretty flaky with SQL Server. I use the backup/restore process to copy databases bevtween servers. Oftentimes the log files will mean the backup file is large and I was looking for a way to minimise that. – Ben Aston Jan 13 '09 at 10:27
  • The log file size has no relation to the backup file size. The back up file contains used pages from the MDF + any entries from the LDF that changed during backup. The files are recreated on restore... so it may appear they are there. – gbn Jan 13 '09 at 11:25
  • @gbn if i have a sql server db 1 gb and 5 gb log file ( ldf) - and i dont need the log transactions....is there any wasy to backup only the 1 GB ? ( or alternately - reset the ldf and backup the ldf+mdf) ? – Royi Namir Feb 12 '12 at 10:01
  • @RoyiNamir: you only backup data anyway. The files are created on RESTORE – gbn Feb 12 '12 at 14:39
  • 1
    @gbn If I backup the data only anyway - So how Does he generates a 6 GB LDF on restore ? where those 6 gb came from ? – Royi Namir Feb 12 '12 at 14:48
  • @RoyiNamir: I just said "The files are created on RESTORE". The restore process reads the backup metadata to get the file sizes, and creates (or replaces them). But your backup is just data though. – gbn Feb 12 '12 at 16:47