Locating the mdf,log,and back up files?

Question

Post reply

Locating the mdf,log,and back up files?

SQLPain

Hall of Fame

Points: 3522
More actions
August 21, 2015 at 9:19 am

#299936

Hi

I am trying to locate the mdf,log and bak files but I am not seeing folder (MSSQL10_50.MSSQLSERVER) the only folder I see there are 90,100,110 and Report builder.

I located the properties of one of the databases below right clicking the properties on SSMS, but when I physically go there, I don't find that folder.

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

I gave myself all the permissions under the security tab fro the folder Microsoft SQL Server but still nothing.

Please advise

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ed Wagner SSC Guru Points: 287015 More actions · Answer 1

Maybe you're not looking in the right place. Try running this query:

select name, type_desc, physical_name

from sys.database_files;

Then look in the path/file in the physical_name.

Maybe the DBA didn't set up the data and log files on the C: drive.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

SQLPain

Hall of Fame

Points: 3522

August 21, 2015 at 9:32 am

#1821738

Thanks, the following query gives me

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

Is it possible that it is being referenced to the above folder but the files are somewhere else?

Ed Wagner SSC Guru Points: 287015 More actions · Answer 3

So you're currently in the master database. That's the file the system looks at to find the file, so that's where it should be.

Also, if you have permission to grant yourself permission, then you probably have permission to read the contents of the folder.

Do you have something in your Windows Explorer options to hide files or something weird like that?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

SQLPain

Hall of Fame

Points: 3522

August 21, 2015 at 9:51 am

#1821746

I did checked the hide options and there is nothing hidden in the folder also.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

Are you looking in the server or your personal computer? Are they the same or different machines?

Remember that those are local paths for the server but might not be local for you.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 6

A couple questions that are worth covering, even though they seem a bit silly:

1) Are you perhaps looking at Program Files, and not Program Files (x86), or vice versa?

2) Are you connected to an instance on the machine you're logged in to? If you're connected to an instance on another server, then obviously that could cause some problems when you start to browse to file paths on the local machine.

Cheers!

SQLPain Hall of Fame Points: 3522 More actions · Answer 7

I am looking at my personal machine, so the path below it is pointing to be has to be the server.

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

I know this might sound to novice but how I do I figure out where the server is located and if in future If I need to play with. mdf,log, .bak files would I be able to? example I had to restore one of the databases today but unable to locate the .bak files

SQLBill SSC Guru Points: 51440 More actions · Answer 8

mufadalhaiderster (8/21/2015)

I am looking at my personal machine, so the path below it is pointing to be has to be the server.

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

I know this might sound to novice but how I do I figure out where the server is located and if in future If I need to play with. mdf,log, .bak files would I be able to? example I had to restore one of the databases today but unable to locate the .bak files

1. Who handles your servers? That's where you need to start. Give them the name of the server (that's the name you connect to in SQL Server Management Studio).

2. They should be able to grant you access (either direct or remote, depending upon your company's security policies) to see the files...but that would be based on your company's policies.

3. You should never be 'playing' with the mdf, log, and .bak files.

4. Are you doing backups of the database(s)? If so, then you should know where they are being save to and be able to do a restore.

-SQLBill

Lowell SSC Guru Points: 323482 More actions · Answer 9

mufadalhaiderster (8/21/2015)

I am looking at my personal machine, so the path below it is pointing to be has to be the server.

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

I know this might sound to novice but how I do I figure out where the server is located and if in future If I need to play with. mdf,log, .bak files would I be able to? example I had to restore one of the databases today but unable to locate the .bak files

you can query an instance find out the default folder location for the data,log and backup folders, but its not uncommon that the data is blank.



DECLARE @Result TABLE (ServerName varchar(128) DEFAULT @@SERVERNAME,ValueRead varchar(256),DataRead varchar(256))

INSERT INTO @Result(ValueRead,DataRead)

      EXECUTE [master].dbo.xp_instance_regread

                N'HKEY_LOCAL_MACHINE',

                N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

                N'DefaultData'

INSERT INTO @Result(ValueRead,DataRead)

      EXECUTE [master].dbo.xp_instance_regread

                N'HKEY_LOCAL_MACHINE',

                N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

                N'DefaultLog'

INSERT INTO @Result(ValueRead,DataRead)

      EXECUTE [master].dbo.xp_instance_regread

                N'HKEY_LOCAL_MACHINE',

                N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

                N'BackupDirectory'

SELECT * FROM @Result

now, since any given database can be placed elsewhere than the default paths, you have to query each database , like with a loop that calls EXEC ('USE [' + @CurrentDB + '] EXEC sp_helpfile ') for each db that is online.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287015 More actions · Answer 10

Luis Cazares (8/21/2015)

Are you looking in the server or your personal computer? Are they the same or different machines?

Remember that those are local paths for the server but might not be local for you.

Doh! I didn't even consider the possibility of looking on the local machine. And here I was scratching my head all during lunch.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions