Automatic Backup/restore

• Bouchakour

  Newbie

  Points: 6

  More actions

  March 12, 2007 at 10:28 am

  #177120

  Hi all,

  Windows 2000 SP4/ Sql server 2000 standard edition

  I have two databases located in two different servers and I like to execute a copy from the first server to the second one. I tried to use export/import using DTS but doesn't work well.

  Now I'd like to planify automatic backup/restore between the two databases (from source to target).

  How can I do that ?

   

  Thanks for any suggestions.

  Hassan

• MohammedU

  SSCoach

  Points: 19644

  More actions

  March 12, 2007 at 4:15 pm

  #694523

  Create a dts package with two EXECUTE SQL task.. one should run the backup on the source server and second should run the restore on destination server...

  Note: Database Backup file location should be accessed by both servers...

   

  MohammedU
  Microsoft SQL Server MVP

• Bouchakour

  Newbie

  Points: 6

  More actions

  March 13, 2007 at 2:54 am

  #694605

  Thanky you Mohammed for your answer.

  I just begin work with dts (from Entreprise Manager) and I do not have experience. Please could you give me one exemple: Sql task to backup and restore and how I can execute these Sql tasks from the dts.

  Best regards,

   

  Hassan

• MohammedU

  SSCoach

  Points: 19644

  More actions

  March 13, 2007 at 1:53 pm

  #694811

  In execute sql task you have to use backup command...

  BACKUP DATABASE <DB NAME> TO DISK = '\\SERVERNAME\D$\DBNAME.BAK'

  In another execute sql task use restore command check BOL for syntax

  RESTORE DATABASE <db name> from disk = '\\SERVERNAME\D$\DBNAME.BAK'

   

  MohammedU
  Microsoft SQL Server MVP

• Bouchakour

  Newbie

  Points: 6

  More actions

  March 14, 2007 at 3:02 am

  #694871

  Thansk u a lot mohammed.

  last question : how can I add the sql tasks to a new DTS ?

  Best regards,

   

  Hassan