Copying detached databases over the network with T-SQL

• Kushal Koolwal

  Ten Centuries

  Points: 1080

  More actions

  July 25, 2006 at 1:37 pm

  #113108

  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

• Stephen Turner

  Mr or Mrs. 500

  Points: 530

  More actions

  July 25, 2006 at 2:12 pm

  #651465

  I don't know about tsql but in the job you can have a cmdexec that has a call to xcopy which would do the job.

• Kushal Koolwal

  Ten Centuries

  Points: 1080

  More actions

  July 25, 2006 at 6:38 pm

  #651518

  are you hinting towards xp_cmdshell??

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  July 26, 2006 at 7:08 am

  #651605

  get hold of robocopy ( from the windows resource kit ) and use xp_cmdshell to create the cmd line to do the file copy.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/