Posted by Erland Sommarskog on 08/14/07 21:47

Bill E. (billmiami2@netscape.net) writes:
> I'm trying to restore a database backed up a production machine onto
> my development machine. I don't want to restore the transaction logs
> (there are two) because they are far too large and are unnecessary for
> what I'm doing. I would be happy to simply create one new log in my
> development environment. Therefore, I backed up the database using
> the No_Log option as in
>...
>>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.
> The database cannot be recovered because the log was not restored.<<
>
> What additional step should I take to create a log file? I thought
> that No_Log was supposed to give me a truncated log file but it didn't
> seem to do so.

Books Online says about NO_LOG:

In the context of a BACKUP DATABASE statement, specifies that a backup
will not contain any log. This equates to how file backups were created
before SQL Server 2005. A database backup created with NO_LOG equates
to a full set of file backups that contains no log records.

Under the full recovery model, NO_LOG is useful if you need to back up
data quickly, and you have a complete sequence of log backups of that
data.

The last paragraph implies that you are expected to apply the transaction
logs from elsewhere.

There is a reason why this does not work as you had expected: you
see, that log serves a purpose. A database backup is taken online, which
means that pages can be updated while it's working. Therefore the
backup must include log records, so that transactions that were committed
when the backup was running can be rolled forward. And more importantly,
transactions that had not yet been committed when the backup completed,
must be rolled back. Thus a database restored from a backup with no log
content is in a inconsistent state, and thus you are not permitted in.

I would suggest that you do a regular backup/restore, and then shrink
the log files once you have restored the database and set the recovery
mode to simple. A tip is to add WITH COPY_ONLY to the BACKUP command.
That prevents the backup from being recorded as a "real" backup.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]