Sql-server – Restore MSSQL backup that was done with NO_LOG option

backupsql serversql-server-2005

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

Best Answer

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.

Related Topic