Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Location of Master and mssqlsystemresource Databases

Posted on 2011-04-24
17
Medium Priority
?
1,068 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 ????
 
0
Comment
Question by:itsonlyme4
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 35456045
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.
0
 

Author Comment

by:itsonlyme4
ID: 35456522
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.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 35457170
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 28

Expert Comment

by:imran_fast
ID: 35458315
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35458342
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
0
 

Author Comment

by:itsonlyme4
ID: 35459197
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?
0
 

Author Comment

by:itsonlyme4
ID: 35459311

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.

0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35459460
Is KOCSQL05 your default instance? Are you connecting localy to this instance?
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35459595
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.
0
 

Author Comment

by:itsonlyme4
ID: 35459812
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 ?
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35459906
Are you using SSMS? Try ADMIN:localhost
You can issue DAC using command prompt:
sqlcmd -A
or
sqlcmd -S localhost -A
0
 

Author Comment

by:itsonlyme4
ID: 35460030
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.  
0
 

Author Comment

by:itsonlyme4
ID: 35460468
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. ??
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35460885
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
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35461079
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

0
 

Author Comment

by:itsonlyme4
ID: 35461355

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.
0
 
LVL 15

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35461911
Right, you are using SQL 2005, I totally overlooked it.
So you've got two pair of mssqlsystemresource database files. To be sure which one is actuallu in use try to move it to other location while instance is running. You will not be able to move files in use. When you know which are your actual files you can edit their location using follwoing procedure:

PROCEDURE TO MOVE MASTER AND RESOURCE DATABASE IN SQLSERVER2005

Sorry for whole confuse.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question