I have my application installed on one server and SQL server installed
another system, which my application uses to log the data and rerieve the
data.

I want to check for the physical mdf and ldf files, if they are present, i
need to make sure that link is proper. I am using "dbcc checkdb" to make sure
that link is proper. If the link is not existing, i need to attach the
database.How can i run sp_attach_db on local system only and ensure that the
database is attached.

Is it possible to use sp_attach_db for remote system directly from
application server having datafiles in remote server?

Is there any other way, which can be used to create a link, when database
files are there?



I am executing sp_attach_db through osql

osql -E -S "ml330g3" -i "c:\two.sql"

where two.sql is having

sp_attach_db 'PMP_V4_142237218','\\ml330g3\administrator\c$\Program
Files\Microsoft SQL
Server\MSSQL\Data\PMP_V4_0_1422372181.mdf','\\ml330g3/administrator\C$\Program Files\Microsoft SQL Server\MSSQL\Data\PMP_V4_142237218_log.LDF'
go

I am getting the error:

Msg 5110, Level 16, State 2, Server ML330G3, Line 1
File '\\ml330g3\c$\PMP_v4_0_18391566.mdf' is on a network device not supported
for database files.

when i executed  DBCC TRACEON(1807) and the same command i get the following
error:

Msg 5105, Level 16, State 4, Server ML330G3, Line 1
Device activation error. The physical file name
'\\ml330g3\c$\PMP_v4_0_18391566.mdf' may be incorrect.

How can i avoid the above errors.

You need to specify the physical drive letter and path for sp_attach_db. For
example:

C:\Program Files\Microsoft SQL Server\MSSQL\Data\PMP_v4_0_18391566.mdf

no server name or shares permitted.

In order to run sp_attach_db you need to login to the server with a login
that has the dbcreator or sysadmin role.

Hi Uday

See inline:

You could also check the tables sysdatabases and sysfiles for this
information.

You can only attach files on local drives for the server being used. This
can be executed remotely.

IF you are connected to ServerX, then running

EXEC sp_attach_db @dbname = N'pubs',
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
   @filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'

Will attach the files from the c:\Program Files\Microsoft SQL
Server\MSSQL\Data\ directory on ServerX and not on the local PC.
Show quoteHide quoteEXEC sp_attach_db 'PMP_V4_142237218','c:\Program Files\Microsoft SQL
Server\MSSQL\Data\PMP_V4_0_1422372181.mdf','
C:\Program Files\Microsoft SQL Server\MSSQL\Data\PMP_V4_142237218_log.LDF'
go


Show quoteHide quoteJohn
Show quoteHide quote

Triggers - run for multiple updated
parsing varchar fields
INSTEAD OF INSERT trigger
Why does update run differently standalone vs in procedure?
SELECT different columns based on the value of another column