Have you ever wondered how movement of system DB datafiles, could affect your SQL Server?
Recently, I came across a situation wherein I had moved the Model/MSDB datafiles from the contemporary location (C:\…\MSSQL\Data) to a separate drive using the following method-
1) ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path\os_file_name’ ) 2) Stopped SQL Server services. 3) Moved the file or files to the new location. 4) Restart services using Config. Manager and services came up fine. 5) Verified the file changes by running- SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Everything seems to be working fine as per expected and changes were reflected in sysaltfiles table after the above process was followed.
But the moment I removed the files from the actual location (C:\…\MSSQL\Data) & restarted the Windows server, SQL Server services didn’t come up automatically (Tried Manually as well) and in turn, there was an error in Event Viewer/Error log –
Event ID: 9003
The log scan number (19:492:1) passed to log scan in database ‘model’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
It clearly indicated the corruption of Model DB.
I couldn’t start SQL Server (Not in single user mode with trace 3608 and with -m option), so there was no point in restoring the Model DB with previous night backup.
To overcome this issue, I copied the model datafiles from the same server (Under C:\…MSSQL\Binn\Templates) to my desired location and tried starting SQL Server services. They came up smoothly without any issues :).
Now, you must restore model backup because you have taken files from the RTM build.
Things are working fine and as per expectations.
Moral of the story:: Remember, we have a template folder in default installation directory from where we can extract the datafiles, in case of such issues.
Hope this helps you too.