SQL Server 2k5 - Backup Of Database Without Log File(s)

Question

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)?

Answer 1

truncate the logs and shrink the files

Answer 2

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).

Answer 3

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.

Answer 4

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.