Attach a sql2005 database at a different directory

[Database Administrator]

I create a database ("a") in SQL 2005 Express using Microsoft SQL Server
Management Studio Express.

I then detach the database "a", copy the two file in C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to another directory d:\test\

I then try to attach it. It gives the following messages. What should I do
to succesful attach a database in a different directory?

Attach database failed for Server 'ASUSTANG2\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
Unable to open the physical file "D:\Test\a.mdf". Operating system error 5:
"5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

[Database Administrator]

Under what account is the SQL Server service running? Does that account
have read/write privileges on that directory?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"David" wrote in message
news:990411BC-958D-481A-B679-C6DCEEE120AB@microsoft.com...
I create a database ("a") in SQL 2005 Express using Microsoft SQL Server
Management Studio Express.

I then detach the database "a", copy the two file in C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to another directory d:\test\

I then try to attach it. It gives the following messages. What should I do
to succesful attach a database in a different directory?

Attach database failed for Server 'ASUSTANG2\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
Unable to open the physical file "D:\Test\a.mdf". Operating system error 5:
"5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

[Database Administrator]

Hello,

Use SQL Server configuration manager from SQL Server 2005 program
goups...There select sql server 2005 services and choose sql server service
and see the Logon account.
If the account is not local system then change it to Local system and then
restart the service and see if the attch works. If it works then give the
necessary previlages to the
toher account and restart the service and see if the attach works fromm D
Drive..

Thanks
Hari

"David" wrote in message
news:990411BC-958D-481A-B679-C6DCEEE120AB@microsoft.com...
>I create a database ("a") in SQL 2005 Express using Microsoft SQL Server
>Management Studio Express.
>
> I then detach the database "a", copy the two file in C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to another directory
> d:\test\
>
> I then try to attach it. It gives the following messages. What should I do
> to succesful attach a database in a different directory?
>
> Attach database failed for Server 'ASUSTANG2\SQLEXPRESS'.
> (Microsoft.SqlServer.Express.Smo)
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> Unable to open the physical file "D:\Test\a.mdf". Operating system error
> 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)
>

[andrewzino]

Dear Hari,

Thank you so much for your insructions - I have manged to get my database back online.

You are a legend!

Andrew

[bimkaz269]

thanks hari, it worked

[RickRoyston]

I recently upgraded from Vista (32-bit) to Windows 7 (64-bit). Although I carefully backed up all my data files, I was unaware that I was supposed to create a specific data backup file from within Business Contact Manager. As a result, I found myself unable to restore my Business Contact Manager database.

I'd tried to restore my data using various methods that I'd found in various online posts, including:
- running a specific SQL script;
- changing permissions on the Business Contact Manager folders and files;
- Starting Outlook in Administrator mode; and
- reinstalling Business Contact Manager.

None of these methods worked. Then I found yours, and it worked perfectly!

Many thanks for sharing your expertise. I'll be surprised if I'm the only Windows 7 upgrader who benefits from your posting!

Thanks

Rick in Connecticut