Restoring from backup with little disk space on SQL Server 2005

Question

At my company we have an SQL Server 2005 install on the c: drive of a virtual machine. All the actual database files live on a separate (virtual) data drive, E:. The c: drive is tiny and has about a gig and a half of disk space left, while E: is enormous.

I have a database, stored on E:, which I'm trying to restore from a backup, also stored on E:. But when I try, SQL Server tells me that

There is insufficient free space on disk volume 'C:\' to create the database. The database requires 3804626944 additional free bytes, while only 1791596544 bytes are available.

...which is true, as far as it goes: C: really does have that much space left. But why would that matter? The .bak file, the .mdf and .LDF, they're all on E:!

How can I convince SQL Server 2005 to create its temporary files, or whatever it thinks it needs all that disk space for, on E: where there's plenty of room?

Answer 1

Are you trying to restore over the existing database on E:? If so SQL Server shouldn't need space on C: at all.

It sounds like you're either trying to restore another copy of the database onto C: (which you won't be able to do unless there's enough space) or the backup was taken of the database when its files were on C:, and so it's trying to recreate them on C:.

You can check this by doing a RESTORE FILELISTONLY on the backup and it will tell you the database files it will try to create (see Books Online here). If they're pointing at C:, you can make the restore create them on E: instead using the WITH MOVE syntax.

You can see an example of this in Books Online at http://msdn.microsoft.com/en-us/library/ms186858.aspx#restoring_db_n_move_files

Hope this helps!

Answer 2

can you expand the size of the virtual hard drive?

Answer 3

If you're using the SQL Server Management Studio GUI tools, hit up the Advanced tab in the restore dialog and make sure the paths are correct for the target data files. Sounds like the source DB was on C: -- and that's what those paths would then be.