0

I have 2 separate SQL Server instances, for the purpose of the question, we will call them Source and Target.

I have an ever changing database on Source called MyDatabase, this database is populated once a day from a piece of software. When this is dumped, I need to create a copy of the database on Target. It must be an exact replica.

My first method was using a SSIS package, to export and import the data, however will this method work if the database schema is changing?

For example, my package copies:

Target.Table1 (ColA, ColB, ColC)
Target.Table2 (ColA, ColB, ColC)

After my package has been set up, the schema becomes:

Target.Table1 (ColA, ColB, ColC)
Target.Table2 (ColA, ColB, ColD) (ColC has been dropped)
Target.Table3 (ColA, ColB)

How can I insure the changes in Table2 are picked up, and Table3 is exported and imported?

Sorry I can't give much more of an example, however hopefully this is enough detail for the question.

11
  • I personally do this with a power shell script and windows task scheduler, backing up daily to azure blob storage
    – Caius Jard
    Dec 11 '18 at 20:50
  • @CaiusJard could this be achieved to copy this to another instance of SQL Server, and do you have an example of the script/
    – Harambe
    Dec 11 '18 at 20:50
  • Have you looked into SQL Server Replication?
    – Ryan Wilson
    Dec 11 '18 at 20:51
  • @RyanWilson I have not, could you provide a link or explain more?
    – Harambe
    Dec 11 '18 at 20:54
  • 1
    Replication is the way to do this. If you can't set up replication, back up the database to a file daily and restore it to the target server. This keeps things like constraints, indexes and sequences intact.
    – Patrick Tucci
    Dec 11 '18 at 21:02
1

There are various high availability technologies. See the following and resources below:

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Use replication to distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise. Replication to SQLCE 3.5 and SQLCE 4.0 is supported on both Windows Server 2012 and Windows 8.

Log shipping Like Always On availability groups and database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases (referred to as secondary databases) for a single production database that is referred to as the primary database. For more information about log shipping, see About Log Shipping (SQL Server).

Database mirroring. Note! This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use Always On availability groups instead. Database mirroring is a solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database. For more information, see Database Mirroring (SQL Server).

Backup/Restore:

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/high-availability-solutions-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-2017

0

I think the easiest way, if you need the database to be an exact copy of the Source one (including login and permissions) would be to simply Schedule a restore of the database.

You probably already have (you should have) a backup strategy in place. Simply make sure you Schedule your full backup of "source" have it have been populated then use that full backup to restore it Under "target".

You can use PowerShell, SQL Agent job with TSQL command or SSIS package to achive the database restore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.