Restore SQL Server DB without transaction log

sql-server restore transaction-log
PhantomDrummer picture PhantomDrummer · Dec 7, 2011 · Viewed 74.1k times · Source

Given a SQL Server 2008 .bak file, is there a way to restore the data file only from the .bak file, without the transaction log?

The reason I'm asking is that the transaction log file size of this database is huge - exceeding the disc space I have readily available.

I have no interest in the transaction log, and no interest in any uncompleted transactions. Normally I would simply shrink the log to zero, once I've restored the database. But that doesn't help when I have insufficient disc space to create the log in the first place.

What I need is a way to tell SQL Server to restore only the data from the .bak file, not the transaction log. Is there any way to do that?

Note that I have no control over the generation of the .bak file - it's from an external source. Shrinking the transaction log before generating the .bak file is not an option.

Answer

Marko Krstic picture Marko Krstic · Feb 7, 2014

The transaction log is an integral part of the backup. You can't tell SQL Server to ignore the transaction log, because there is no way to let's say restore and shrink the transaction log file at the same time. However, you can take a look at DBA post to hack the process, although it is not recommended at all

Alternatively you could try some third party tools for restoring, particularly virtual restoring process that can save a lot of space and time. Check out ApexSQL Restore, RedGate Virtual Restore, Idera Virtual Database.

Disclaimer: I work for ApexSQL as support engineer

Related questions

SQL Server: Database stuck in "Restoring" state

I backed up a database: BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.bak' WITH INIT --overwrite existing And then tried to restore it: RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE --force restore over specified database And now the database …

Read More
"No backupset selected to be restored" SQL Server 2012

I have a SQL Server 2012 database with filestream enabled. However, when I backup it and try to restore it on another SQL Server 2012 instance (on another machine), I simply get this message that: No backupset selected to be restored. Not …

Read More
When restoring a backup, how do I disconnect all active connections?

My SQL Server 2005 doesn't restore a backup because of active connections. How can I force it?

Read More