How to copy database from one SQL Server to another automatically by using your software?

In following instruction : Server A is the source SQL Server , Server B is the target one.

Requirements:

  • Both server have SQL Server Backup software installed.
  • Both SQL server's have same database edition , at lease A is lower than B's SQL Server.
  • If your servers have direct network connection , Source server A should has a profession edition of SQL Server Backup installed , and a std edition at lease installed on server B.
  • If Server A and B is on same machine ,you need only one license SQL Server Backup software.

If your servers have direct network connection , that is easy ,there two ways:

1 . Copy database by using backup and restore jobs:

  • Server A : add target backup agent in SQL Instance configuration window.
  • Server A : create backups job to create database backup to a backup device.
  • Server A : create a restore job to restore backup to target backup agent.
  • Server A : Schedule backup and restore job to run automatically.

For this way , when the restore job runs , it always starts from a full backup . if your database is a big one , please do not use this method.

2 . Copy database by using log shipping job:

  • Server A : add target backup agent in SQL Instance configuration window.
  • Server A : create log shipping job that copy database from server A to B .
  • Server A : Schedule this log shipping job.

For this way, if you let server B' target database in standby state , it dose not start from full backup every time , and most of time , it only copies the modified data from server A to B. this method is recommended.

If your servers have no direct network connection , and the backup is copied manually via USB drive .etc:

  • On server A , create a backup job for source database.
  • On server B , create a backup device for A's backup.
  • On server B , import backups on this device. this step need to be done only once.
  • On Server B , create restore job that use these imported backup and run once.
  • On Server B , after restore job runs once , re-configure this job and enabled import backup automatically from shared device ,then schedule it to copy database periodically.
  • Copy backup periodically from server A to B's backup directory.