11

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.

1 Answer 1

6

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.
2
  • Yes the problem is that my server doesn't have enough space to store the backup file, and the restored database. The issue of the log 'growing, growing' yeah true, but that is up to our database admin to deal with - that is not my responsibility.
    – peter
    Nov 30, 2011 at 1:23
  • If that's the case, use the hack to restore to a compressed folder. Dec 6, 2011 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.