Automation of backup of table with index

• ranjani2.s

  SSC Journeyman

  Points: 77

  More actions

  October 8, 2009 at 9:58 am

  #212015

  Hi All

  I need to take back up of few tables in my database (Sql 2005) and restore them to another database. My Intention is both the database must be in sync

  There are two ways i) restore the main tables ii) restore the database.

  If i do the first option using select * into tablename from tbl ,am not getting the indexes restored.

  I i do the second option can any one explain how to automate the backup of one database and restore it to another database

  Thanks.

• Mark Marinovic

  SSCrazy

  Points: 2472

  More actions

  October 8, 2009 at 10:12 am

  #1063576

  Take a look at the following topics on BOL or search here at SSC in the archives:

  1. Log Shipping

  2. Replication

  3. Database Mirroring (SQL Server Enterprise, 2005+)

  I am sure others will chime in with direct links to articles written on the subjects.

  MJM

• homebrew01

  SSC Guru

  Points: 55544

  More actions

  October 9, 2009 at 8:37 am

  #1064111

  I scheduled a nightly restore to a test system a while back:

  1) Backup to a drive (scheduled)

  2) Batch file to copy .BAK to destination server (Step 2 of backup)

  3) Destination server has job to run restore command

  4) Restablish sql logon permission to restored database.

  But Mark gave good suggestions for getting data to the other server. Some ideas are better than others depending on what you need.