57

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.

Oreo
  • 529
  • 3
  • 16
LondonPhantom
  • 1,867
  • 3
  • 19
  • 26
  • As a late side note, the restoration time is an issue too, especially if you're doing development and manual testing against databases and swapping them out on a regular basis – Savage Apr 12 '16 at 07:21

5 Answers5

9

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

Community
  • 1
  • 1
Marko Krstic
  • 629
  • 6
  • 5
7

No, the transaction log is required.

Option 1:

An option may be to restore it to a machine that you DO have enough space on. Then on the restored copy change the logging to either bulk logged or simple, shrink the logs, do another backup operation on this new copy and then use that to restore to the target machine with the now much smaller transaction log.

Option 2:

Alternatively, perhaps the contact at the external source could shrink the transaction log before sending it to you (this may not work if the log is large due to a lot of big transactions).

Docs on the command to shrink the log file are available here.

Chris Townsend
  • 3,042
  • 27
  • 31
  • Thanks. I can't do anything about the .bak file, so guess it'll be a case of finding a machine that does have sufficient disc space. – LondonPhantom Dec 07 '11 at 14:44
  • 9
    Out of interest, does anyone know *why* MS didn't provide an option to only restore the data, not the transaction log? I would presume that the only reason the TL in the restored db is so large is that .bak file must contain a flag that says 'please reserve so much space for the TL when you restore', and there doesn't seem to be any logical reason why the restoring process shouldn't be able in principle to ignore such a flag. So I'm intrigued why that option isn't there. For my particular .bak file, the space required for the TL is 50GB, although this is invariably almost entirely empty space. – LondonPhantom Dec 07 '11 at 14:48
4

This is really a question for the ServerFault or DBA sites, but the short answer is no, you can only restore the full .bak file (leaving aside 'exotic' scenarios such as filegroup or piecemeal restores). You don't say what "huge" means, but disk space is cheap; if adding more really isn't an option then you need to find an alternative way of getting the data from your external source.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
3

This may not work since you have no control over the generation of the .bak file, but if you could convince your source to detach the database and then send you a copy of the .mdf file directly, you could then attach the .mdf and your server would automatically create a new empty transaction log file.

See sp_detach_db and sp_attach_db (or CREATE DATABASE database_name FOR ATTACH depending on your sql server version).

Aaron
  • 383
  • 1
  • 5
  • That doesn't work for me. It wants the log file. Well it created the database, even though it errored, so maybe it worked? I don't know if this causes problems later? – John Foll Jul 28 '22 at 18:35
0

I know this is an old thread now, but i stumbled across it while I was having transactional log corruption issues, here is how I got around it without any data loss (I did have down time though!)

Here is what I did:--

Stop the sql server instance service make a copy of the affected database .mdf file and .ldf file (if you have an .ndf file, copy that as well!) - Just to be sure, you can always put these back if it doesn't work for you.

restart the service.

Log into sql management studio and change the database mode to simple, then take a full backup.

Change the database type back again and once again take a full backup, then take a transactional log backup.

Detach the database.

Right click on databases and click on restore, select the database name from the drop down list, select the later full database backup created (not the one taken from the simple mode) and also select the transactional log backup.

Click restore and it should put it all back without any corruption in the log files.

This worked for me with no errors and my backups all worked correctly afterwards and there were no more transactional log errors.

Filfish
  • 1
  • 1