About deleting mdf file

SQL Server Administration
1

Hi,

Is it ok to delete a database mdf file from window explorer under the circumstance describe below?

It is during a restore process and the server doesn’t have enough space.

I have run alter db set single_user with rollback immediate, and will run restore from disk with replace.

Since that file is going to be replaced anyway, is it ok to delete it first then re-run above restore to get by insufficient free space issue?

Never done this way before, but logically it should seem OK.

What do you all think?

Thanks!

2

It would not be okay to delete just the MDF file but you could drop the database which would delete the files. You are then committed to performing the restore as your only course of action. I have done this before and for just the reasons that you are facing. One small syntax item though. In your original restore script you might have the REPLACE option. This option requires that the database exist. You would need to remove this option.

1 Like
3

If you are restoring over an existing database using REPLACE - and getting insufficient space errors, deleting the files won't resolve the issue. You will still need to add space to the drives or relocate the files to a different location that has enough space.

1 Like
4

Can you elaborate or clarify? I often perform restores to our development environment with the REPLACE option and it is not uncommon to get the out of space error. When I delete the original database and restore the newer version, the error goes away. No additional space required; issue resolved.

1 Like
5

When you use replace - SQL Server is going to overwrite the existing files. So if the existing mdf file is taking up 50GB of space that space will be reused and considered as available when you start the restore process.

If you are getting out of space warning then you are changing the name of the files so the original files are not actually being overwritten.

I often restore a 6TB database that is spread across multiple mount points - where the file is taking up all of the available space on that mount point. I have never had a warning stating I am out of space...

Another downside to dropping the database and files prior to restoring is that SQL Server then has to rebuild those files. That can cause the restore process to take much longer - especially if instant initialization is not enabled.

1 Like