April 2, 2013 at 5:34 pm
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
April 2, 2013 at 6:03 pm
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.
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
April 2, 2013 at 6:11 pm
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
April 2, 2013 at 7:03 pm
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
April 3, 2013 at 1:37 am
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
April 8, 2013 at 5:52 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply