Restore database with zero size log file

Question

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

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. — , May 09 '13 at 12:45

score 7 · Answer 1 · answered May 09 '13 at 12:50

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.

answered May 09 '13 at 12:50

Remus Rusanu

51,846
4
95
172

I believe the above answer is completely wrong. – Ian P May 09 '13 at 14:14
1

@IanP: no, is correct. – Remus Rusanu May 09 '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 09 '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 09 '13 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 '13 at 14:29

score 2 · Answer 2 · answered May 11 '13 at 15:13

2

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

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

answered May 11 '13 at 15:13

Cougar9000

1,538
1
13
29

score -1 · Answer 3 · edited Aug 26 '13 at 04:43

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

edited Aug 26 '13 at 04:43

Aaron Bertrand

180,303
28
400
614

answered May 09 '13 at 14:13

Ian P

151
2

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

Restore database with zero size log file

3 Answers3