question

sroscoe avatar image
sroscoe asked

SQL SERVER MANAGEMENT STUDIO 2005 problem

I had something quirky happen today, I was doing a database restore from Management Studio 2005. I selected my backup file, then went to the options folder, selected overwrite the existing database. Now here's my problem, I click the ellipsis to select the file location, I navigate to the folder where the .mdf file is located and I cannot see the mdf files I logged into the server to verify that the files were at the location I had specified and they were. Why can I not see the mdf files? The .ldf files are on another drive and I can see them!
sql-server-2005restore
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Never seen that one myself sorry. I would try refreshing the view. Then I'd log off & log back on. Then I'd try restarting SSMS. If none of that worked, I'd go and check security to validate something hasn't changed in my persmissions (those are the permissions you'd be browsing under, not the servers) if I had previously been able to see stuff. Also, check silly things like that the extension said what you thought it said when you went to that directory (was it MDF or NDF, that sort of thing). If it's none of those, I'd file a bug with Connect.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks for the reply. additional information... I tried all of your suggestions before asking the question. I did everything short of restarting sql server. I was in the past (last week) able to see all of the mdf files using the restore wizard. I tried logging directly to the server and using management studio there and I still can't see the files, even though I can see them through Windows Explorer.
0 Likes 0 ·
sqlnubi avatar image
sqlnubi answered
Also when all else fails, REBOOT.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
My bet is that you copied the MDF files to the directory and somehow managed to do that in a way that the account that SQL Server is running under did not have access to the files. Check the security on the files themselves to make sure that SQL Server's service account has full access.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nope, did not touch the mdf files. Permissions are correct. I did notice that if I marked the files are ready for archiving, I could then see the files. I am going to have the server rebooted during the maintenance window tonight and see if this clears up anything
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
were you using SSMS on a different computer from where you were doing the restore? The folders you navigate in the wizard are the server folders, not local drives. Are they standard file types - mdf, ldf, ndf or something else? Are permissions OK - did you open SSMS with SQL or Windows Auth? Does the SQL Service account have permission? Sorry if its repeats/stuff you have tried already ... this is a weird one!
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I tried SSMS from my desktop and from the actual server where the restore was being done. I have done this many times, just last week as a matter of fact for the same db. They are the standard files mdf, permissions are ok. If I check the file properties on the server and go to advanced and check files ready for archiving, then I can see the mdf file from my desktop and from the server itself using SSMS
0 Likes 0 ·
are there multiple instances on the server? I know I have jumped up and down the directories a lot when I have been in the wrong one! Also is it a 64 bit server? - may be program files vs program files (x86)?

Just to prove a point can you try putting the files in the root of a drive and see them from the wizard without the archive setting?
0 Likes 0 ·
ok, I did what you suggested Fatherjack and I think now we are on to something. my path is program files/microsoft sql server/mssql.1/mssql/data. I took the db offline and copied the mdf file to each folder (program files, microsoft sql, etc). I was able to see the file (without archive setting) in all of the folders except the data (last) folder. So, now what should I be looking for...I have checked the permissions and they look the same.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Have you tried doing the restore via T-SQL to see if that works, regardless of GUI silliness?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes, I got the restore completed. Just wondering if any one had seen this weird issue. Server will be rebooted tonight
0 Likes 0 ·
Ha! Should have thought of this one. +1. It's how I do all my restores anyway.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.