Not able to restore database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Not able to restore database

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-05-02 : 19:59:01

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
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-02 : 22:39:35

Do you have proper sql permission? How did you restore db?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-05-03 : 10:28:06

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

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-05-03 : 11:25:32

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-03 : 12:55:08

Did you connect to sql server with sa?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-05-04 : 17:10:12

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-04 : 22:38:27

Where did you run the command? On the server directly or from client machine? Are db files on server's c:\ drive?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-05-06 : 13:14:11

Yes, I ran the command on Server itself. Just not able to get any help.Still thanks for your continuos reply.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-06 : 22:20:59

Where are db files? On server's c:\ drive?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-05-07 : 18:24:23

Yes,they are on server C drive.I have checked the security permissions too and all folders have full permissions.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-08 : 10:33:22

Checked permission on the file? What's sql service startup account?

jyushinx
Starting Member

2 Posts

Posted - 2007-05-16 : 15:40:09

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.

jyushinx
Starting Member

2 Posts

Posted - 2007-05-16 : 16:46:45

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.

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-16 : 20:38:02

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.

fyz
Starting Member

4 Posts

Posted - 2008-05-21 : 09:49:33

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-21 : 22:36:03

We were talking about sql server here.

manoj_jdubey
Starting Member

1 Post

Posted - 2008-05-25 : 00:08:22

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)

ComeInSki
Starting Member

1 Post

Posted - 2009-07-21 : 18:33:56

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'

WhimSQL
Starting Member

1 Post

Posted - 2009-10-29 : 17:56:33

I got this to work by right clicking the folder and removing the "read only" attribute.

~whimsql

bhupender
Starting Member

1 Post

Posted - 2010-02-25 : 01:27:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources