How can we help you today? How can we help you today?

Restoring a SQL Server 2005 database without the log

Every night I restore backups of my databases to a report server. I don't need the logs, so after each restore I detach the database and delete the log. When I reattach, SQL Server creates new log files that are tiny. If I can do this, why do I need the log files in the first place? As it is, I have to stagger my database restores because I don't have room for the 400 GB of log files.

I know that if I backup my database with the no_log option, I will have to provide a log file for it to restore. This allows for online backups where the database is updated during the backup process. I don't do any updates to my database during the backup. Is there any way to flag the backup file as not needing a log file? There should be no uncommitted transactions in the backup. Would taking my database offline during the backup help?

Thanks
Keith_Walton
0

Comments

6 comments

Sort by
  • petey
    When you perform a full database backup, SQL Server will back up the active portion of the transaction log, which starts from the oldest active transaction.

    Could you please run

    DBCC OPENTRAN

    when connected to the database and confirm that there are no long outstanding transactions?

    Thanks.
    petey
    0
  • Keith_Walton
    I have added this to my backup job for tonight.

    If there is no active portion of the transaction log, will it restore with no log? The restore currently creates a log that is the exact same size as the original, so shrinking the log file before backup helps when I run out of disk space (the 400 GB I mentioned earlier is after shrinking).
    Keith_Walton
    0
  • petey
    I seem to have misunderstood your original question, my apologies. I was under the impression that you though SQL Server was backing up unnecessary transaction log data.

    A full database restore will always create transaction log files of the same size as that used by the database during the backup. So yes, physically shrinking the transaction log using DBCC SHRINKFILE or DBCC SHRINKDATABASE would help in reducing the space equirements when the database is restored. However, note that the shrinking process may not always succeed on the 1st attempt (see this link, and shrinking the log when it will eventually grow to the pre-shrink size again has adverse effects (see this link).
    petey
    0
  • Keith_Walton
    My production database needs big log files, which is fine. My read-only report database does not, especially since the logs are empty. I was just hoping there is a way to restore a database without the logs or with log files that were smaller than the original. I wonder if I can backup a database with "no_log", and then restore it with a "fake" empty log file that I create.
    Keith_Walton
    0
  • Keith_Walton
    petey wrote:
    When you perform a full database backup, SQL Server will back up the active portion of the transaction log, which starts from the oldest active transaction.

    If only the active portion of the transaction log is backed up, what does my restore operation take longer as the log grows? One of my databases is 170 GB, and its log file compresses to about 120 GB. After compression, it will grow as big as 250 GB before we compress it again. The bigger it gets, the longer the restore takes (up to twice as long). It seems like all the restore should be doing is creating an empty log file the same size as the original.
    Keith_Walton
    0
  • petey
    The NO_LOG option does not do what you want. It only tells SQL Server to truncate the transaction log without making an actual backup to disk/tape.

    The restore is taking longer because SQL Server is, as you stated, ' creating an empty log file the same size as the original'. If the file is large, it will certainly take a while.
    petey
    0

Add comment

Please sign in to leave a comment.