Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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" ?

share|improve this question

migrated from stackoverflow.com May 11 '13 at 3:18

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Jon Seigel, StanleyJohns, Max Vernon, Remus Rusanu, Mark Storey-Smith May 12 '13 at 4:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
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 '13 at 12:45

3 Answers 3

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.

share|improve this answer
    
I believe the above answer is completely wrong. –  Ian P May 9 '13 at 14:14
1  
@IanP: no, is correct. –  Remus Rusanu May 9 '13 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 '13 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. –  Remus Rusanu May 9 '13 at 14:19
1  
+1 this is the right answer. –  Thomas Stringer May 11 '13 at 15:27

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

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

share|improve this answer

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

share|improve this answer
    
I dont mean shrink an existing log, I'm talking about restoring a backup but not restoring the empty log file. –  codeulike May 9 '13 at 14:18
    
And if you restore file by file somewhere, shrink, backup and restore on your limited drive? –  Ian P May 9 '13 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 '13 at 14:50
    
Well I was thinking, was it possible, was it easy. Not was it quick!!! –  Ian P May 9 '13 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 '13 at 17:19

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