Restoring a SQL Server 2005 database without the log

Keith_Walton · July 22, 2008 2:17PM

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

petey · July 23, 2008 5:15AM

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.

Keith_Walton · July 23, 2008 11:14AM

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

petey · July 23, 2008 11:58AM

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

Keith_Walton · July 23, 2008 12:09PM

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 · July 23, 2008 4:28PM

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.

petey · July 24, 2008 4:08AM

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.

Restoring a SQL Server 2005 database without the log

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk