3

I am trying to restore a SQL Server 2008 (standard edition) backup to my dev machine using the production database. I get an "insufficient free space" error when doing so. SQL Server claims that it needs 43 GB free to restore my db, but the .bak file is only 4.5 GB! Sure, I'll give it a little room to operate, but 10x the amount of actual space used seems a little excessive. I am not compressing this db (when I do compress the .bak file, it goes down to .8 GB).

Is there anything I can do to the original db to allow it to be installed within a smaller working space?

ep4169
  • 157
  • 2
  • 4
  • 9

2 Answers2

1

Is it likely your transaction log file...the backup will want to create one as large as was on the original.

Your best bet if this is the case is to shrink the log file before you do the backup.

Another option outside of backup if you can get away with it is to detach, and then copy the database file and re-attach, SQL will then create a new transaction log file at the default size of like 1mb.

keithwarren7
  • 186
  • 2
  • 8
  • So should I clean out the original? How often is this generally done? – ep4169 Oct 29 '09 at 17:55
  • There are maintenance plan options for shrinking it but it really depends on your operational needs. Shrinking will lose transactional data from the past but if that is not of concern to you then it is something you can consider. – keithwarren7 Oct 29 '09 at 18:26
  • Taking another approach, is it possible to specify the transaction log file size to allocate when doing the restore? – ep4169 Oct 29 '09 at 18:37
  • No, unfortunately you cant. – keithwarren7 Oct 29 '09 at 19:13
0

The 43GB is the combined size (most likely) of the MDF and LDF files.

A backup only takes used pages in the files, so it's entirely possible especially if you have heavy table/index fragmentation or have never managed your log files or never done index maintenance

Fix: more disk space (of course!). Try and restore to a compressed NTFS folder, external USB drive/pen, or another box to generate more temporary storage, sort the data out, backup again, restore "normally"

gbn
  • 6,079
  • 1
  • 18
  • 21