April 2, 2014 at 2:36 am
While trying to move the MSDB and Model DB, I mistakenly ran the following
ALTER DATABASE model
MODIFY FILE (NAME = modelData, FILENAME = 'D:\New folder\model.mdf')
GO
ALTER DATABASE model
MODIFY FILE (NAME = modelLog, FILENAME = 'D:\New folder\model.ldf')
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'D:\New folder\model.mdf')
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBLog, FILENAME = 'D:\New folder\model.ldf')
GO
Now SQl will not start, how do I get it back to normal.
The ldf and mdf names are the same for both dbs.
April 2, 2014 at 4:27 am
Talib123 (4/2/2014)
While trying to move the MSDB and Model DB, I mistakenly ran the followingALTER DATABASE model
MODIFY FILE (NAME = modelData, FILENAME = 'D:\New folder\model.mdf')
GO
ALTER DATABASE model
MODIFY FILE (NAME = modelLog, FILENAME = 'D:\New folder\model.ldf')
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'D:\New folder\model.mdf')
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBLog, FILENAME = 'D:\New folder\model.ldf')
GO
Now SQl will not start, how do I get it back to normal.
The ldf and mdf names are the same for both dbs.
Firstly, the correct filenames should be
model.mdf
Modellog.ldf
Msdbdata.mdf
msdblog.ldf
For now don't bother with the msdb, it's not required anyway to get the instance online, however the model is!
Rename the current log file at the OS level for model from 'D:\New folder\modellog.ldf' to 'D:\New folder\model.ldf', thisd will match the query you executed. That will get the instance back online for you.
Once the instance is up re run the correct commands and stop the service, set the model log file name back to 'D:\New folder\modellog.ldf' and you should be fine after that.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 2, 2014 at 5:24 am
This may be of some help: https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2014 at 2:59 am
Thanks Gail, was hoping you would reply. I'll have a crack with your advise later. Only my sandpit environment so BAU comes first. If I ever get the time to resolve it.
April 3, 2014 at 6:06 am
Talib123 (4/3/2014)
Thanks Gail, was hoping you would reply. I'll have a crack with your advise later. Only my sandpit environment so BAU comes first. If I ever get the time to resolve it.
Have you followed my steps above?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 3, 2014 at 6:48 am
Ah Perry didn't see that was you thought that was Gail too.
My bad!! Simple mistake although my script had a typo. The file it was looking for was MODEL.log. WHen in fact the file was still Modellog.ldf.
All in all some lessons learnt about recovery and in the comfort of my own SANDPIT environment. No sweaty palms and the bad breath of managers on my neck watching me work under pressure.
Thanks both. I would of been stuck.
April 3, 2014 at 6:56 am
Talib123 (4/3/2014)
The file it was looking for was MODEL.log. WHen in fact the file was still Modellog.ldf.
Yes, so if you follow my steps above you'll recover with no issues
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply