August 21, 2015 at 9:19 am
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
August 21, 2015 at 9:27 am
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.
August 21, 2015 at 9:32 am
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?
August 21, 2015 at 9:37 am
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?
August 21, 2015 at 9:51 am
I did checked the hide options and there is nothing hidden in the folder also.
August 21, 2015 at 9:56 am
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.
August 21, 2015 at 9:56 am
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!
August 21, 2015 at 10:14 am
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
August 21, 2015 at 10:24 am
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
August 21, 2015 at 11:21 am
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
August 21, 2015 at 11:25 am
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.
August 21, 2015 at 1:00 pm
thanks guys..this forum is really helpful
August 21, 2015 at 2:09 pm
I'm glad it's helpful, but did you figure out the answer to your original question?
August 21, 2015 at 3:11 pm
my DBA is out today.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply