SQL Server - How to copy database daily 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.
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