2

SQL Server backups contain meta-data about the size of the original log (LDF) file.

So if you backup a database that has a 5GB LDF file, when you restore it, you will get an (empty) 5GB LDF file. This often isn't needed (because the log file of the restored database contains practically nothing)

Often this causes problems with database restores on busy dev servers with not much space left if people *cough* haven't been too careful about shrinking log files before making the backup.

Is there any way to restore a SQL Server backup but tell it "Dont bother making that empty 5GB LDF file, thanks" ?

1
  • I would characterise "needing to restore a backup" as a common thing programmers need to do, hence I would claim this question falls under "software tools commonly used by programmers". Thanks.
    – codeulike
    May 9, 2013 at 12:45

3 Answers 3

7

No, there is no way. Restores really is meant to recreate the original database that was backed up. There are tools that allow you to query directly a backup w/o actually restoring it (Idera's SQL Virtual Database) but those are horrendously slow.

5
  • I believe the above answer is completely wrong.
    – Ian P
    May 9, 2013 at 14:14
  • 1
    @IanP: no, is correct. May 9, 2013 at 14:15
  • See my answer, if I understand the original post yours is wrong: or tell me why my solution wont work?
    – Ian P
    May 9, 2013 at 14:18
  • 3
    @IanP: you are restoring the 50Gb and then shrink. the question is whether you can restore w/o creating the 50GB file. May 9, 2013 at 14:19
  • The question is/was can you restore and eliminate or minimise the log file size, yours is the first mention of 50GB, still if you want to mak it up as you go along that ok by me.
    – Ian P
    May 10, 2013 at 14:29
2

Simple answer, no. The database being restored must match the source database.

this is not the fix you seek (waives hand dramatically)

-1

Well you might have to go through another step or two: restore on another server, shrink, backup, copy and restore. If you restore in simple recovery mode (or switch to simple recovery mode which can be done with the db online), the log database will be (or can be shrunk to) as small as it can get. You can then switch to full recovery when the finance department decides that the potential data loss cost is more expensive than the additional storage.... In SQL Server Management Studio, Rt mouse click on the database and select properties, then go to the options tab, second drop down list from the top. Then go to File sizes and shrink.... Wasn't even difficult, might be time consuming...

5
  • I dont mean shrink an existing log, I'm talking about restoring a backup but not restoring the empty log file.
    – codeulike
    May 9, 2013 at 14:18
  • And if you restore file by file somewhere, shrink, backup and restore on your limited drive?
    – Ian P
    May 9, 2013 at 14:20
  • Yeah, that would work, but takes ages with a big database. I'm asking whether there's a better way, sounds like there isnt. However if you edit your answer to say 'restore on another server, shrink, backup, copy and restore' then it would qualify as a reasonable answer to the question I guess.
    – codeulike
    May 9, 2013 at 14:50
  • Well I was thinking, was it possible, was it easy. Not was it quick!!!
    – Ian P
    May 9, 2013 at 15:50
  • Actually, you can probably trim the time a bit by simply detaching copying and attaching files from the intermediate machine. Unless your using a backup restore that compresses on the fly.
    – Ian P
    May 9, 2013 at 17:19

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