Thursday, March 22, 2012

Attaach 2000 data file from network drive

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
BanuYou 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" <abuslu@.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|||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:

> 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" <abuslu@.hotmail.com> wrote in message
> news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
>
>|||Banu_tr wrote:
> Dear Ryan,
> I execute the dbcc traceon(1807) . But still couln't see the mapped networ
k
> 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/defaul...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
--|||Dear David,
I still got the message "Device Activation Error"
any advice?
"David Portas" wrote:

> Banu_tr 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/defaul...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
> --
>|||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
--|||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
--

No comments:

Post a Comment