Can't restore because log file too big...

[JennyPeters]

Hi,

I can't seem to restore my database because the log file is too big for the space left on my drive. There are a number of reasons for this, to prevent this from happening again... but in the meantime, does anyone know how to restore a .bak file, using only the .mdf and not the .ldf? The restore tries to expand the whole file, and that's where the size issues comes in.

Thanks,

Jenny

 

[mrdenny]

Jenny,
Unfornitually the solution to your problem is to purchase more disk space. To do a restore you need to be able to restore the database as it was when backed up. Huge files and all. There is no way to restore the mdf without the ldf. You've got to restore them both. And unfornitually there is no way to split the ldf or shrink it while restoring it. You have to wait until after the restore is finished.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JennyPeters]

Ok, thanks Denny.

I'm actually using a 160gig external hard drive to do the job, though so far it's taken over 30 minutes with no progress bar indication. According to the backup file list, the log was 55 gigs, which seems ridiculous to me. The .bak file was only 91meg...

Thanks,

Jenny

 

[mrdenny]

There was probably a lot of free space in the Log. If it was going to fail because there wasn't enough space it would have already. One of the first things that restore database does is check that there is enough space. If there isn't it will through an error message. It is probably creating the empty 55 Gig file. It takes an annoyingly long time to create large files when restoring.

When the database is backed up, and the file has free space, that free space isn't saved in the backup, as there is no need to back it up. So SQL simply marks how big the file is supose to be, and creates a file that large when creating the database. It then puts in what ever data it has. Once the database is done restoring, shrink the files down to a more managable size. Be sure to account for growth so that you don't have to worry about the database extending the files to often, but they won't take forever to restore next time.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JennyPeters]

Thanks Denny,

The retore finally finished after 14 hours!! And you were right, there was 50 gig of free space, and 4 gig of info. The only why to finally delete the free space at the end was to change the recovery mode to 'simple', back up, change to 'full' and back up again.

SImply shrinking the DB or BU didn't remove the space allocated back to 4 gig.

So odd.

Thanks for your help!

Jenny

 

[mrdenny]

The data probably needed to be moved around within the file. Your method works just fine. You can check out more info about shrink the log files under "DBCC SHRINKFILE" in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)