Recently I was moving a SQL Server model database log file to another disk. I made an
ALTER DATABASE [model] MODIFY FILE... command a bit too fast and pasted the destination from a file browser window. Of course this set the log directory as the new log file (...\MSSQL\Data).
I also restarted the instance before realizing my mistake, so now it wouldn't start. How to fix this? Well I tried renaming the data folder and then renaming the data file to the folder's original name Data. This didn't solve the problem.
Fixing the file location
I found the answer from this article on moving system databases. First you have to start the SQL Server instance with a couple of flags:
NET START MSSQLSERVER /f /T3608
Next you need to use the sqlcmd command line tool to correct the database file location. Open the command line with admin privileges.
-- Open the connection sqlcmd -S tcp:INSTANCENAME/INSTANCENAME -U login_name -P password -- Correct the log file location ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', FILENAME = N'...\MSSQL\Data\modellog.ldf' ) GO
Close the connection with
exit and restart the instance. Everything should be working fine now! Just run a couple of queries to verify that everything's okay.
That's how you can recover from misplaced system database files. This method can also be used for database data files. Luckily the instance in question didn't have any real databases on it. Remember to be careful when moving database files!