Copying detached databases over the network with T-SQL

I have a T-SQL script which deataches and attaches the database. Now here is what I want to do:
I

want to create a SQL Server Job and schedule it to run at a particular

time of the month to Detach all the databases on my local machine and

*Copy all the deatched databases over a network machine* and then

Attach those Database.

All this should be done in T-SQL. I

already have the T-SQLs for Attaching and Detaching. All I need is to

know the T-SQL (example) which will copy the *Detached Databases* from

mu local computer to my Network Computer. How do I achieve that?

Thanks

xp_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

xp_cmdshell 'copy c:\x.mdb y:\x.mdb'

where y: is a mapped network drive

|||You can invoke DOS command through sqlserver (e.g. "copy <source> <\\target>"). You want to take a look at xp_cmdshell in book online for details.|||

aside from using the sql jobs you can use

the "windows scheduler" to

1. detach the database from the source server using sqlcmd

2. use dos command to copy the database

3. use sqlcmd to attach the db to the destination server

I recommend this method over the other

cheers

|||Thank you guys for you replies. I think I may take the path of xp_cmdshell.

joeydj your suggestion looks valid, but I have a question. What is the full command(an example) to detach the database using sqlcmd? Also do I need to run this command on 'Command Prompt'.

Thanks
|||You can use sp_detach_db to detach a database. See BOL for syntax and examples. It is easier doing these type of operations outside the database. Note that in SQL Server 2005 xp_cmdshell is disabled my default on most SKUs & enabling it increases the security risk on the server. So don't use it unless you absolutely need to. Writing a batch file to do these operations is very trivial.|||

for sql2k you can use OSQL the SQLCMD 2005 counterpart

and here's the syntax

c:\ osql -USa -P -S(local)\sql2k -Q"sp_detach_db demodb" -X

where

-Usa --user Sa

-P -- password in my case blank

-S(local)\sql2k --is the server

-Q"sp_detach_db demodb" -- this is the query to detach and watchout for the quotes

-X exit OSQL

sqlcmd has the same syntax except that it handles blank password differently

c:\ SQLCMD -USa -Pmypaswd -S(local)\sql2k -Q"sp_detach_db demodb" -X

you cann use the -E switch for trusted connection

for more help type sqlcmd/? or osql/? on your command prompt