Home Forums SQL Server 2005 Backups SQL server 2005 Backup & restoration size RE: SQL server 2005 Backup & restoration size

  • Is your database set to Full Recovery mode? If so, you need to be backing up the log files too.

    When you back up a database, it backups up the data and the definitions of the database. Part of the definition is how much space the database has been allocated. If your log files are huge AND empty, then you may need to shrink the file, prior to the backup. Then, the backed up database will have a smaller file definition. However, shrinking files can be extremely problematic and should only be approached when you know it's safe. You can make a mess of your system by shrinking files too often or inappropriately. Usually, assuming you are in Simple recovery or Full recovery with log backups in place, the log file is the size it needs to be (assuming auto file growth) to support the transactions on your system. If you are in Full recovery without log backups, the log file will just keep growing.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning