SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Not able to restore database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunil
Constraint Violating Yak Guru

India
282 Posts

Posted - 05/02/2007 :  19:59:01  Show Profile  Reply with Quote
I am having windows vista home premium. I have installed SQL Server Express 2005 Advanced SP2. When I try to restore any database i get the following error.

Restore failed for Server 'VARNIKASINGH-04\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. (Microsoft.SqlServer.Express.Smo)


Also , when i try to attach the database, I get access denied error.

Any help is welcomed.

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/02/2007 :  22:39:35  Show Profile  Reply with Quote
Do you have proper sql permission? How did you restore db?
Go to Top of Page

sunil
Constraint Violating Yak Guru

India
282 Posts

Posted - 05/03/2007 :  10:28:06  Show Profile  Reply with Quote
How do i see permissions set on database. I am logging in as administrator.I am able to create database and add tables to it but when i try to attach a database or restore database, i get the "access denied" error.When I try it at work, it works fine.I feel its due to windows vista and tried to google also, but to no help.I installed SQL server with user "sa". Do i need to change the user?How can i do that?Thanks for your reply.

Regards
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 05/03/2007 :  11:25:32  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
I had an issue in SQL 2000 where names over 15 characters were not liked by the sql server.
We had to rename the server to reflect names under 15 characters especially for replication.
Another thing that you may want to try, is to create an alias for your server using the SQL Server Configuration tool/SQL Native Client Configuration/Aliases


Regards
Paresh Motiwala
Boston, USA
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/03/2007 :  12:55:08  Show Profile  Reply with Quote
Did you connect to sql server with sa?
Go to Top of Page

sunil
Constraint Violating Yak Guru

India
282 Posts

Posted - 05/04/2007 :  17:10:12  Show Profile  Reply with Quote
Yes, I log in with sa.I even made a database by running script from one SQL book.Yesterday when tried to attach database i got following:
When I am trying to attach Advetureworks database by using following script, i get the following error.I am logged in as administrator.What kind of permission i have to set so that i can install the database.Any help will be appreciated.Script is :

EXEC sp_attach_db @dbname = N'AdventureWorks',

@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf',

@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf' ;

Error is :

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf". Operating system error 5: "5(Access is denied.)".




I even checked permissions on "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" folder. it has all permissions and that Long SQL Sever user also has full control over the folder.Thanks for yor reply.

Regards
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/04/2007 :  22:38:27  Show Profile  Reply with Quote
Where did you run the command? On the server directly or from client machine? Are db files on server's c:\ drive?
Go to Top of Page

sunil
Constraint Violating Yak Guru

India
282 Posts

Posted - 05/06/2007 :  13:14:11  Show Profile  Reply with Quote
Yes, I ran the command on Server itself. Just not able to get any help.Still thanks for your continuos reply.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/06/2007 :  22:20:59  Show Profile  Reply with Quote
Where are db files? On server's c:\ drive?
Go to Top of Page

sunil
Constraint Violating Yak Guru

India
282 Posts

Posted - 05/07/2007 :  18:24:23  Show Profile  Reply with Quote
Yes,they are on server C drive.I have checked the security permissions too and all folders have full permissions.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/08/2007 :  10:33:22  Show Profile  Reply with Quote
Checked permission on the file? What's sql service startup account?
Go to Top of Page

jyushinx
Starting Member

2 Posts

Posted - 05/16/2007 :  15:40:09  Show Profile  Reply with Quote
I am having this same exact problem. It is most definitely an issue with Vista. I attempted to restore the same database on a different system with XP and it worked fine.

I have yet to find a fix.
Go to Top of Page

jyushinx
Starting Member

2 Posts

Posted - 05/16/2007 :  16:46:45  Show Profile  Reply with Quote
I figured out the issue.

Doing some more research online, I discovered that this was a Vista permission issue. In the error, it states that it received an 'Access is denied' error when attempting to access a certain directory. In this case, that directory is 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'

All I did to fix this was navigate to the MSSQL folder as listed above and view the Security properties for it. One of the users listed is a SQL Server user (the name looks like a large jumble of text and numbers). I assigned this user account Full Control and the when I attempted to restore my DB, everything went fine.

Hope this works.
Go to Top of Page

hollandstephen
Starting Member

USA
10 Posts

Posted - 05/16/2007 :  20:38:02  Show Profile  Reply with Quote
You would probably also find that you have no problem with this on Vista if you change your default database directory to a directory that is outside the Program Files directory.

Vista protects the Program Files directory by default by not allowing manipulation of the files contained within its sub-directories, from my understanding.
Go to Top of Page

fyz
Starting Member

China
4 Posts

Posted - 05/21/2008 :  09:49:33  Show Profile  Reply with Quote
Hi,

I think you should check the mdb file and make sure it is ok. You may need to replace the mdb file with a backup or try some recover tools. I have used an utility called Advanced Access Repair to repair my Access MDB file. Its web address is http://www.datanumen.com/aar/ It works well but not free, so I think it can be used in the end. Hope this helps.

Alan


fyz
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/21/2008 :  22:36:03  Show Profile  Reply with Quote
We were talking about sql server here.
Go to Top of Page

manoj_jdubey
Starting Member

USA
1 Posts

Posted - 05/25/2008 :  00:08:22  Show Profile  Send manoj_jdubey a Yahoo! Message  Reply with Quote
It seems you do not have permission on C:\ Programm files.
When you tried to take the SQL files under C:\ and if you do not have permission on that you will get the same error message.

I would recommed you to create a new DB on the server and keep Data and log file on Desktop and also make sure you should be having Windoes admin and SQL admin access on the server.

If you do not have either of the permission, it will fail again you will get same error message.

Manoj
(MCP,MCTS)
Go to Top of Page

ComeInSki
Starting Member

New Zealand
1 Posts

Posted - 07/21/2009 :  18:33:56  Show Profile  Reply with Quote
Check the restore options and make sure you have sufficient space for the Data and Log output files on the target disk. I got this same error and discovered that C drive (default is Program Files/SQL Server/...) was full.

Replace 'Can' with 'Should'
Go to Top of Page

WhimSQL
Starting Member

1 Posts

Posted - 10/29/2009 :  17:56:33  Show Profile  Reply with Quote
I got this to work by right clicking the folder and removing the "read only" attribute.

~whimsql
Go to Top of Page

bhupender
Starting Member

1 Posts

Posted - 02/25/2010 :  01:27:13  Show Profile  Reply with Quote
Go To --> Configuration Tools --> SQL Server Configuration Manager

Now select SQL server 2005 Services and in the right Pane double click "SQL Server(SQLEXPRESS)" and in the built-in Account select "Local System"

After that restart the service, Then run your command to attach the .mdf files to your DB..

It will work
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000