I am scripting the copy of databases from one server to another. Both are running SQL Server 2005 and the databases are in the Full recovery mode.

The transaction logs on these databases can get quite large and I don't need them on the server they are being copied to. (i.e. I don't need to be able to restore to any point in time, I just want a copy of the database at the moment the backup was performed). So if possible I'd like to avoid the time taken to peform the log backup, copy across a slow network and restore.

I am trying to use the "NO_LOG" backup option to achieve this. This produces a backup ok but when I try restore the backup onto the target server the database remains in the 'Restoring' state and can't be accessed. I am assuming this is because it is expecting me to restore the transaction logs.

Is there any way I can get around this and create a new empty transaction log? Note that I can't just truncate the transaction logs on the source server before I do a backup (without NO_LOG) as they are important.

If not what other options are there for getting a copy of a database other than backup/restore? I have already tried the "transfer" method which scripts out all the objects and data but this is too slow for my needs due to the large number of objects.


Edit: Here are the commands used

BACKUP DATABASE FrontEnd TO DISK='c:\somepath\abackup.bak' WITH NO_LOG, COPY_ONLY

RESTORE DATABASE FrontEnd FROM Disk='c:\somepath\abackup.bak' WITH RECOVERY

The response to this command is

Processed 1944 pages for database 'FrontEnd', file 'DimensionPrototype' on file 1.
The database cannot be recovered because the log was not restored.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE DATABASE ... FILE=<name> successfully processed 1944 pages in 0.923 seconds (17.253 MB/sec).
  • Please show us the code you're using to Backup or Restore the DB. – MartW Oct 14 '09 at 12:06
  • Good point! I've added the commands and response to the question body. – rnwood Oct 14 '09 at 12:16

I expect the easiest way to do this is to shrink your Frontend database's transaction log back to its initial size and backup the database, then grow the transaction log back to an approriate size.

You should do this at a quiet time where minimal transactional activity is happening.


You don't need to use NO_LOG with the COPY_ONLY option to accomplish this. The COPY_ONLY full backup will be sufficient to restore with recovery. When you restore the database, it is going to create the log and data files to the sizes they were when the backup was taken. You can't get around that. NO_LOG is truncating your transaction log, so you can't do a point in time recovery of your primary database after you run this. You need to start your backup set over by doing a standard full backup if you have issued this command.

  • Wow, that's a surprise! The documentation for the NO_LOG option doesn't mention that it will truncate the log – rnwood Oct 14 '09 at 13:31
  • Robert - Check out Paul Randal's blog post on this subject. sqlskills.com/blogs/paul/post/… – Jonathan Kehayias Oct 15 '09 at 1:20
  • actually, NO_LOG when used with the BACKUP DATABASE command doesn't truncate the log as it does with BACKUP LOG, from MSDN: 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. – Nick Kavadias Oct 15 '09 at 12:24

First off there seems to be some misconception here that the entire transaction log (.ldf file) ends up in your backup file. This is not the case. SQL Server copies enough of the transaction log so the restore operation can recover the database.

The reason you cannot restore the database is because you need restore a log backup on top of the backup you've just restored to allow the database to perform a recovery. SQL Server cannot bring a database online from a restore without a log backup. For more information see this ms kb article.

There's no way around this. Suck it up & go buy some more disk space.

  • +1 Although sometimes only 1GB of a 300GB transaction log is needed to complete the restore. Given that "buying more disk space" is a 1+ year process were I work, it would've been nice if there was an option to restore only the needed part of the transaction log... – Andomar Feb 5 '11 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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