August 17, 2008 at 6:16 pm
I was following the steps outlined here but when I went to move the master DB I hadn't yet configured sqlcmd to talk to the DB.
Now I can't use SSMS or SQL Configuration Manager to connect to the DB. All I can do is from the command line.
I can stop or start the default instance using the command line but I need to know how to start the DB with default setup so I can connect with the Configuration manager and/or the surface utility to allow sqlcmd to talk to the DB, so I can complete the master DB data and log file move.
I'm a complete noob and the only SQL DBA we have at work is on vacation for the next week.
BTW, this is a new install on a two node cluster, SQL 2005 std on server 2003.
August 17, 2008 at 7:03 pm
I'm not sure I understand what you did.
The steps for master are to shut down the db. Then copy the files to a new location and edit the path in config manager or services and then restart the server. There is no config for config manager or SQLCMd to connect to the server.
August 17, 2008 at 7:15 pm
I can't use the gui sql configuration manager to connect and change the startup params, and the surface util wont connect 'in a timely manner'.
I got as far as stopping the DB and then starting it from the cmd line with net start mssqlserver /f /T3608 but then I need to run this command from sqlcmd and I can't get surface util to connect so I can allow sqlcmd.
This is the command I have to run from sqlcmd:
USE master;
GO
alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME='NEW PATH\mssqlsystemresource.mdf');
go
alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME='NEW PATH\mssqlsystemresource.ldf');
go
alter database mssqlsystemresource set READ_ONLY;
go
August 17, 2008 at 7:21 pm
The surface utility does not need to connect.
SQLCMD should connect if the database is started. When you start it from the command line, do you see all databases recover in the command window and the server started?
August 17, 2008 at 7:40 pm
I got to chat to our SQL DBA for a minute and she suggested I rebuild the master DB. How so I do that remotely? Microsoft says run setup.exe but that fails with the gui. Going to try command line.
August 17, 2008 at 8:15 pm
I wouldn't do that. Rebuilding is a serious step.
Did you see the server startup? Is this a remote server you are connecting to through RDP?
August 17, 2008 at 8:23 pm
Yes, I built it friday on site and today I got the bright idea to move all system databases from the D drive to the K drive. I didn't rebuild the master db yet. I am at home now connecting over RDP.
I just rebooted the server and I can connect with surface util, config manager, etc. I THINK I still need to run the commands regarding the resource database I mentioned before. What do you think?
By the way I REALLY appreciate your input late in a sunday evening.
I just ran the commands from before and I got this:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'. No entry found with that name. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'. No entry found with that name. Make sure that the name is entered correctly.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'mssqlsystemresource', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
August 17, 2008 at 9:34 pm
As a side note, when getting SQL Surface Area Manager to connect to a clustered SQL Server, you have to change the computer name to the virtual SQL Server name (it defaults to localhost).
Did you move the mssqlsystemresource.mdf & mssqlsystemresource.ldf files into the same folder that you moved the master.mdf & mastlog.ldf files to?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 12:07 am
Hi,
Actually whenever we resotr the master database into server ,it always refers/look the default location od master's ldf anf mdf files...so please make sure that your mdf and ldf must be reside in
"Microsoft SQL Server\MSSQL.1\MSSQL\Data"...? 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 18, 2008 at 4:13 am
bhuvnesh.dogra (8/18/2008)
Hi,Actually whenever we resotr the master database into server ,it always refers/look the default location od master's ldf anf mdf files...so please make sure that your mdf and ldf must be reside in
"Microsoft SQL Server\MSSQL.1\MSSQL\Data"...? 🙂
if you modified the path of mdf /ldf of master to new path in the sql server configuration manager , so you can start service in the new location
you can change the default location 😉
for more information see:
http://msdn.microsoft.com/en-us/library/ms190737.aspx
Using the SQL Server Service Startup Options
August 18, 2008 at 6:14 am
Yes, the resource database and log files are in the same folder as the master db.
August 18, 2008 at 6:56 am
Sorry I signed off last night. Had some family issues.
Are you connecting to SQL Server via a sysadmin/sa account? You will need those to move the resource database. Same for the starting with a trace flag.
The procedure should be:
- stop SQL
- copy files (resource MDF/LDF)
- start SQL with trace flag (either command line or add to services applet)
- ALTER database to new path
- stop SQL
- remove trace flag
- start SQL Server.
August 18, 2008 at 7:35 am
I don't know if this helps? I encountered a similar problem when I detached a database which was marked as the default database for my Windows user Id. Once detached I only had connection errors from Enterprise Manager.I resolved the problem by logging in as sa and changing the default database for my Windows User Id.
August 18, 2008 at 11:09 am
These are all helpful, I will check them out later. I am waiting for a SA to install Sharepoint. Do you think theses problems will lead to any issues with the Sharepoint install?
August 18, 2008 at 11:12 am
You want to be sure the SQL Server is running OK, not sure if the Resource database causes issues with Sharepoint, but there is some integration with Sharepoint.
Worst case, put the resource db back where it was, restart SQL , should be OK. Might need to take down Sharepoint later to move them, but that could be scheduled.
Resource database shouldn't grow too much, so it shouldn't be a space issue.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply