Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

Thanks

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).
share|improve this question
    
Please show us the code you're using to Backup or Restore the DB. –  CodeByMoonlight 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
add comment

3 Answers 3

up vote 0 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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