We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Location of Master and mssqlsystemresource Databases

itsonlyme4
itsonlyme4 asked
on
Medium Priority
1,136 Views
Last Modified: 2013-11-05
I am running several SQL Server 2005 Instances (SP3with CU4) on a Windows 2008R2 Platform.

After I installed the instances I moved the System Databases to a drive other than their original install location and then I must have applied the service packs after I moved the databases so I ended up with 2 sets of the mssqlsystemresource.ldf and mssqlsystemresource.mdf files.

I have since realized that moving the system databases (master, msdb and model) from their original locations is not good practice so I will be moving them back.  the model and msdb will not be a problem to move but my dilema lies with the master DB and the 2 sets of mssqlsystemresource.ldf andmssqlsystemresource.mdf files.  

Is there a way to tell either by some query or through searching the registry to find out which set of mssqlsystemresource files to keep?

I have one set here \\kocsql05\e$\Microsoft SQL Server\MSSQL.3\MSSQL\Data dated 10\15\2011 (which is the original install directory)
and one set here: \\kocsql05\f$\MSSQL\KOCSQL05\Sharepoint\Datafiles dated 9\26\2009 (which is where I moved the master.mdf file to)

I'm ASSUMING that I should keep the newest mssqlsystemresource files (in the original install location)   but I am not sure.   any thoughts on how to rectify this and get the master database files back into the original install location with the correct mssqlsystemresource files ????
 
Comment
Watch Question

Top Expert 2006

Commented:
run it on master database.

select * from sys.files;
or just open management studio right clich mater database properties and then files it will show you the location.

Author

Commented:
select * from sys.files;
gives me..
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.files'.

select * from sys.database_files
shows me the database files for the master database but not for the mssqlsystemresource database.

I am trying to figure out how to safely move my master database back to it's original install location and also pair it up with the correct set of mssqlsystemresource database files.

Commented:
Assuming you are just interested of the master...

use master
go
select *
from sys.sysfiles


or run this to get the correct path of the mdf and ldf files.
sp_helpdb master
Top Expert 2006

Commented:
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,
The mssqlsystemresource is hidden database. To be able to check it's files location please do as following:

run command prompt
- start->run->(type) cmd

stop SQL Server
- (type) net stop mssqlserver

start SQL Server with 3608 trace flag
- (type) net start mssqlserver /f /T3608

then start SSMS but do not connect (please assure that SQL Agent doesn't start), click on New Query and type ADMIN:<your instance name> (e.g. ADMIN:kocsql05)

next run following query, you'll get your mssqlsystemresource database files locations:
 
SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767

Open in new window


after that you need to stop SQL Server

run command prompt
- start->run->(type) cmd

stop SQL Server
- (type) net stop mssqlserver


Move your files and start SQL Server again:
run command prompt

- start->run->(type) cmd

stop SQL Server
- (type) net start mssqlserver

Author

Commented:
Thank you for your reply!!!  

I I stopped my Default SQL Server instance and restarted it with the 3608 trace flag.

I opened a query window and tried to run the ADMIN: command but couldn't get it right:
ADMIN:KOCSQL05 - syntax error
ADMIN: KOCSQL05 - syntax error
ADMIN: MSSQLSERVER - syntax error  
NOt sure what the ADMIN: command does and why I can't get it to work..

I then ran SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767   from the same query window and  I get 0 rows returned and no results.

any idea what I'm doing wrong?

Author

Commented:

tigin44 suggested:
use master
go
select *
from sys.sysfiles

Problem is I KNOW where my master data and log files are..  I need to find out which 'set' of mssqlsystemresource database files  are the correct ones.

Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Is KOCSQL05 your default instance? Are you connecting localy to this instance?
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
I made some assumptions, I'm sorry. If KOCSQL05 is named instance you control it by using MSSQL$KOCSQL05, so start by using:
net start MSSQL$KOCSQL05 /f /T3608
It would be easier when you connect to the box and perform actions locally. To connect to named instance you need to use <ServerName>\<InstanceName>, e.g. SRV01\KOCSQL05 for named instances. For default you just use computer name. The same applies to DAC connection (the ADMIN: prefix), ADMIN:<ServerName>\<InstanceName> for named instances, ADMIN:<ServerName> for default instance.

Author

Commented:
Daniel.   KOCSQL05 is the Server name and I am working with the Default Instance.  I am also directly on the Server.

That being said..   ADMIN:KOCSQL05 should be correct..  but I am getting a syntax error

I tried the same with one of the named instances on this box :
Stop Named Instance from CMD line:
net stop mssql$AML
start SQL Server with 3608 trace flag
net start mssql$AML  /f /T3608

I then opened a new Query window and tried to run this:
ADMIN:KOCSQL05\AML

This returns a syntax error ?
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Are you using SSMS? Try ADMIN:localhost
You can issue DAC using command prompt:
sqlcmd -A
or
sqlcmd -S localhost -A

Author

Commented:
I'm  still not having any luck with ADMIN:localhost or ADMIN:KOCSQL05 and
SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767

I tried this:
select dbid,fileid,name,filename
from [master].[dbo].[sysaltfiles]
where filename like '%systemresource%'
with no luck.

SELECT * FROM sys.sysaltfiles shows me all data and log file locations except the mssqlsystemresource Database data and log files..

I checked the SQL Server logs and even searched the registry for mssqlsystemresource.  

Author

Commented:
What I ended up doing was picking one of my named instances and moving the master data and log files back to their original install location.   I then reapplied SP3 and CU4 to see if it would change the date on the mssqlsystemresource files.   it did not.  my master DB data and log files are now back in their original install location and I still have 2 sets of mssqlsystemresource data and log files.  I was hoping that it would update the date\time stamp on one set of files so I would know which are the correct ones.   may end up reinstalling all my Instances just to clean things up unless there are more suggestions. ??
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
I'm  still not having any luck with ADMIN:localhost or ADMIN:KOCSQL05 and
SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767

That's probably because you didn't start that instance using /f /T3608 flags.
Try:
net stop MSSQLSERVER
net start MSSQLSERVER /f /T3608
sqlcmd -A
SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767
GO
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Actually you don't need single user mode to check it, so only DAC would be sufficient:
for default instance
(in cmd) sqlcmd -A
SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767
GO

Author

Commented:

I tried:
C:\sqlcmd -A
1> SELECT name,[filename] FROM sys.sysaltfiles WHERE [dbid] = 32767
2> go
name
                                                 filename
-------------------------------------------------------------------------------
------------------------------------------------ -------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
(0 rows affected)

When I just do

C:\>sqlcmd -A
1> SELECT * FROM sys.sysaltfiles
2> go
  I get expected results.
DB Expert/Architect
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.