Corruption - Renaming of system data databases to ldf :(

Question

Corruption - Renaming of system data databases to ldf :(

JayK

SSCrazy

Points: 2679
More actions
April 2, 2013 at 5:34 pm

#292033

Hi,
This morning a made a proper boo boo while building a new SQL Server 2008 R2 install. After the installation had completed I wanted to move the system databases to a new drive.
However when moving the log files I executed the following T-SQL in error:
ALTER DATABASE [master] MODIFY FILE ( NAME = master , FILENAME = 'J:\Log\SystemLog\mastlog.ldf' );
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'J:\Log\SystemLog\modellog.ldf' );
i.e. I renamed the data files to ldf's.....
As a result I cannot start the SQL Engine and get the following error in the ERRORLOG on startup:
2013-04-03 10:12:40.74 spid9s Error: 5171, Severity: 16, State: 1.
2013-04-03 10:12:40.74 spid9s J:\Log\SystemLog\modellog.ldf is not a primary database file.
2013-04-03 10:12:40.77 spid9s Error: 945, Severity: 14, State: 2.
2013-04-03 10:12:40.77 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2013-04-03 10:12:40.77 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
I would rather not have to re-install again. Does anyone have any advice on how to get around this one?
Thanks in advance,
JK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

JayK (4/2/2013)
ALTER DATABASE [master] MODIFY FILE ( NAME = master , FILENAME = 'J:\Log\SystemLog\mastlog.ldf' );
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'J:\Log\SystemLog\modellog.ldf' );
i.e. I renamed the data files to ldf's.....

If I'm reading what you did right, even worse... you overwrote the old log files with the data files and dropped the data files... then tried to startup with it and they got treated like log files.

As a result I cannot start the SQL Engine and get the following error in the ERRORLOG on startup:
I would rather not have to re-install again. Does anyone have any advice on how to get around this one?

Most of the solutions to cure corrupted Master DB's are intrusive and last ditch, but you can try the rebuild master switch with the startup command.

For SQL Server 2008: http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Personally, I'd just reinstall.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

With this being a new install, I'd just go with the reinstall.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JayK SSCrazy Points: 2679 More actions · Answer 3

Hi Jason / Craig,

Many thanks for the replies - I think for my pride I wanted to stick it out and try and solve it without a re-install so I was able to rebuild the system databases with the following command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=****** /SAPWD=****** /SQLCOLLATION=Latin1_General_CI_AS

and then re-executed my corrected script and I'm happy again,

Thanks for the replies!!

JK

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

For future reference:

http://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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EdVassie SSC Guru Points: 60478 More actions · Answer 5

Best Practice is to not move or rename files for master, model and the resource databases. There are no performance or data integrity reasons for making these changes. All that you do by making these changes is to risk the stability of your SQL Server instance.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara