Attaching Databases across networks

[seekwall]

Does anyone know how to attach a database TO another server? I am currently writing an SP that will (hopefully) detach, move and re-attach a database from one server to another. The last bit is causing me problems though.

I can attach a database FROM another server by executing the following code.......but I can't for the life of me reverse the process.

DBCC TRACEON (1807)
execute master..sp_attach_db 'MPS_Client',
@filename1 = N'\\Server\Server_C\Directory\Data.mdf',
@filename2 = N'\\Server\Server_C\Directory\Log.ldf'
DBCC TRACEOFF (1807)

Any ideas would be appreciated

 

[pytchley]

I think that a utility called DBMOVE can move databases across SQL servers

 

[JamesLean]

You could create a stored proc on the target server which takes the data/log file locations as parameters and executes sp_attach_db with them.

Your main SP could then execute sp_detach_db, copy the files and then execute the other SP. This assumes the target server is set up as a linked server on the source server. --James

 

[seekwall]

Thought of that....

However, the target server is always going to change depending on where there is space each month (just to make things more tricky). The target server is currently passed down the SP as a parameter.

Out of interest....how can I execute an SP on one server, from another ? Don't think I've ever tried that

Cheers