Need a script to backup db on one server and restore that db to another server

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mldardy
    New Member
    • Mar 2008
    • 8
    #1

    Need a script to backup db on one server and restore that db to another server

    I need to what is the best way and how to create a script or procedure where I can create a backup of a database on one server(producti on) and restore that database onto another server(test). The database currently exists on both servers but I want to keep the data on test up to date with production server. Does anyone know the best way to do this and possibly how to do this.

    Thanks
    Tags: None
    • ck9663
      Recognized Expert Specialist
      • Jun 2007
      • 2878
      #2
      One word: REPLICATION

      Good luck!!!

      --- CK

        Comment

        • mldardy
          New Member
          • Mar 2008
          • 8
          #3
          Originally posted by ck9663
          One word: REPLICATION

          Good luck!!!

          --- CK
          I am probably gonna need a little more assistance then this. What do I do in Replication? Does replication build a sql backup script for the database on a prod server that I can restore into a database on a test server

            Comment

            • ck9663
              Recognized Expert Specialist
              • Jun 2007
              • 2878
              #4
              It replicates your database into multiple subscribers. Depending on the setting, you may sync the subscribing database every x frequency.

              If the DB is small, you can just backup your production DB, and restore it as a testDB.

              --- CK

                Comment

                • JamieHowarth0
                  Recognized Expert Contributor
                  • May 2007
                  • 537
                  #5
                  In the old days, this kind of job would be a multi-step DTS (Data Transformation Services) transaction, where you would run a SQL script that backed up the database, then ran a shell command to FTP the backed-up database to the new server. On the receiving server, you'd have another DTS job to check for a new file received via FTP in a certain folder - once the file exists, you'd then run a SQL statement to restore it.

                  This functionality is now handled by SSIS (SQL Server Integration Services) from SQL Server 05 onwards and requires Workgroup Edition or above (won't work on Express). I'm not too familiar with it, but MSDN's comprehensive online reference guide should be a good place to start. Failing that, looking at replication (which you can find details on via the above link) would be the other option. Bear in mind your test server has to be Internet-facing for any such solution to not require manual intervention at some stage in the transfer process.

                  Hope this helps.

                  codegecko

                    Comment

                    Previous template Next
                    Working...