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?
Best Answer
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!