NOTICE!! BACKUP DATABASE FILES BEFORE DOING THIS, DO IT AT YOUR OWN RISK!
I don't want anyone sending me hate mail because this did not work, however it does work (most of the time).
If you are getting these errors:
- cannot be opened due to inaccessible files or insufficient memory or disk space
- the log cannot be rebuilt because there were open transactions/users when the database was shutdown
- unable to open the physical file
- the system cannot find the file specified
- error 5120
- huge ldf file, restore from backup hangs at 100%
- HDD IO maxed out on ldf file during restore from .bak file
- how to restore just the mdf
If your getting errors like the above and have tried all other solutions, you can try the following. Be sure to make a backup of your .mdf / .bak file.
In my case I had a database that for some reason created a 600GB ldf file and corrupted itself. While attempting to restore from a backup it would say 100% completed but would continue to run for hours/days. If I stopped the process it would kill the database and not allow me to use it. Here is what needs to be done:
First restore from your .bak file if you have one.
Example code:
RESTORE
DATABASE [dbname] FROMDISK=N'DRIVE:\BACKUPDIR\BACKUPFILE.bak'WITHFILE= 1,NOUNLOAD,REPLACE,STATS=10
GO
in most cases this will fix your database, IF this backup hangs at 100% for too long you can do the following:
now using the resource monitor verify that the mdf file is done writing (VERY IMPORTANT) select disk sort by file, check to see that the disk is only read/write to the .ldf file and not the .mdf file. Once the system has stopped writing to the .mdf file do the following.
- stop mssql service
- go into the DATA folder and rename the dbname.mdf file to dbname.mdfx
- start the mssql service
- delete the database (IMPORTANT, UNCHECK option to delete backups)
- create new database with the same name and give proper user owner access
- stop the mssql service
- delete the dbname.mdf & dbname.ldf files
- rename the dbname.mdfx to dbname.mdf
- start the mssql service and wait a minute for the service to fully start before issuing the following command:
ALTER DATABASE dbname SETEMERGENCY;
GO
ALTER DATABASE dbname SET single_userWITHROLLBACKIMMEDIATE;
DBCC
checkdb('dbname', repair_allow_data_loss);
GO
GO