Attaach 2000 data file from network drive

27 Jan 2006 8:30 AM

Banu_tr

Hi
How can I attach mdf file without copying it to local disk.
I dont have enough space on the server which I have to attach db. But there
is enough space on an other server in the network.

Thanks
Banu

27 Jan 2006 9:09 AM

Ryan

You will need to set this trace flag to enable

dbcc traceon(1807)

Be aware that this isn't entirley supported by Microsoft and any network
delays can lead to corruption. Also performance will suffer...

--
HTH. Ryan

Show quote

"Banu_tr" <abu***@hotmail.com> wrote in message
news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@microsoft.com...
> Hi
> How can I attach mdf file without copying it to local disk.
> I dont have enough space on the server which I have to attach db. But
> there
> is enough space on an other server in the network.
>
> Thanks
> Banu

27 Jan 2006 11:22 AM

Banu_tr

Dear Ryan,

I execute the dbcc traceon(1807) . But still couln't see the mapped network
drive when I try to attach the mdf file.
What else should I do?

Show quote

"Ryan" wrote:

> You will need to set this trace flag to enable
>
> dbcc traceon(1807)
>
> Be aware that this isn't entirley supported by Microsoft and any network
> delays can lead to corruption. Also performance will suffer...
>
> --
> HTH. Ryan
> "Banu_tr" <abu***@hotmail.com> wrote in message
> news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@microsoft.com...
> > Hi
> > How can I attach mdf file without copying it to local disk.
> > I dont have enough space on the server which I have to attach db. But
> > there
> > is enough space on an other server in the network.
> >
> > Thanks
> > Banu
>
>
>

27 Jan 2006 11:51 AM

David Portas

Banu_tr wrote:

> Dear Ryan,
>
> I execute the dbcc traceon(1807) . But still couln't see the mapped network
> drive when I try to attach the mdf file.
> What else should I do?
>
> "Ryan" wrote:

Make sure you refer to the network path with the UNC name rather than a
drive letter. Like:

EXEC sp_attach_db 'JUNK',
'\\SERVER\share\MSSQL\data\JUNK.mdf',
'\\SERVER\share\MSSQL\data\JUNK_log.LDF'

or:

EXEC sp_attach_db 'JUNK',
'\\SERVER\C$\MSSQL\data\JUNK.mdf',
'\\SERVER\C$\MSSQL\data\JUNK_log.LDF'

IMO running a database from a network drive is a near suicidal thing to
do if you care about the integrity or availability of your data. Do
this for a READ-ONLY or SINGLE USER database only. Make sure you have a
current backup BEFORE you attempt it. Don't expect decent performance
or reliability.

Read:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

27 Jan 2006 12:52 PM

Banu_tr

Dear David,
I still got the message "Device Activation Error"
any advice?

Show quote

"David Portas" wrote:

>
> Banu_tr wrote:
> > Dear Ryan,
> >
> > I execute the dbcc traceon(1807) . But still couln't see the mapped network
> > drive when I try to attach the mdf file.
> > What else should I do?
> >
> > "Ryan" wrote:
>
> Make sure you refer to the network path with the UNC name rather than a
> drive letter. Like:
>
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\share\MSSQL\data\JUNK.mdf',
> '\\SERVER\share\MSSQL\data\JUNK_log.LDF'
>
> or:
>
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\C$\MSSQL\data\JUNK.mdf',
> '\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
>
> IMO running a database from a network drive is a near suicidal thing to
> do if you care about the integrity or availability of your data. Do
> this for a READ-ONLY or SINGLE USER database only. Make sure you have a
> current backup BEFORE you attempt it. Don't expect decent performance
> or reliability.
>
> Read:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

27 Jan 2006 1:01 PM

David Portas

Banu_tr wrote:

> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>

Are you sure the server has access to the share you are trying to use?

Same advice as before: don't do it. Not for production use anyway. Tell
your boss, customer or business owner that they need to purchase some
more storage.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

27 Jan 2006 1:16 PM

David Portas

Banu_tr wrote:

> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>

You need to check that the account that the SQL Server service runs
under can access the share where your data files are located. If SQL is
running under the service account then you need to change it to run as
a domain login, give that login the necessary permissions, then stop
and restart the SQL server service. Having done that, it should work
but as already indicated this is not a fully supported option and never
having tried it I don't know under what circumstances it might not work
at all.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--