Sql-server – Backing up database without transaction log

sql-server-2008

When I backup a database is there a way to exclude the transaction log in that backup? Or am I thinking about this wrong and to restore the original database the content of the transaction log is required?

I cannot shrink the transaction log because the database is using replication and I would have to break the replication.

If I issue this command and then restore the database it comes back with a 50 GB log file:

BACKUP DATABASE DatabaseName TO DISK = 'c:\temp\Database.bak'

I have to change the restored database to simple and then shrink the log file before the 50GB is freed. I cannot change the original database to simple because it is a replicated copy.

Best Answer

It looks like your confusing various concepts here. Start with Transaction Log Management and Backup Overview topics in Books Online.

Is the root problem that when you restore the full backup to another server, the transaction log is consuming more space than you have available?

If so, the hack I described in an answer to the question Is it possible to restore sql-server bak and shrink the log at the same time? might be useful.

Is your transaction log growing, and growing, and growing because your database is in full recovery but you aren't taking log backups? See here and here.

When I backup a database to a bak file is there a way to exclude the transaction log in that backup?

No. A data backup will include part of the transaction log, at least the section of the log that was generated since the backup started. It shouldn't however be a significant proportion of the log unless you have very long running transactions.

Note that when you restore a data backup, the database will be recreated with the same data and log file sizes as were present in the source database, regardless of how full the data file was or how large the active part of the log was. If this is the problem you're experiencing, see my earlier hack for working around a lack of space to restore.

Or am I thinking about this wrong, to restore the original database is the content of the transaction log required?

See above, a proportion of the log will be included in a full backup.

I cannot shrink the transaction log because the database is using replication and I would have to break the replication.

No reason why you can't shrink the log with replication in place (you'll only be able to shrink the inactive portion) but:

  • It won't make any difference to the size of your data backup.
  • It will probably grow back to it's current size.
  • The large transaction log is probably due to not taking log backups.
  • You shouldn't be shrinking the log.
Related Question