• It is going to depend on your exact requirements. Does the report database have schema specifically for the reports? It would be common to want different indexes, stored procedures and views in this database.

    If that is the case, you would need to build something to either clear the data and copy fresh data or make the necessary updates. SSIS would be well suited for this purpose, or since it is all on one server, you may want to use a group of T-SQL stored procedures. I think I would lean toward SSIS making the assumption that eventually the databases would be on different servers and I would want to avoid using linked servers at that point.

    If they will have the same structure, why have two databases? If you are trying to have a point-in-time version of the database, you may want to look into database snapshots.

    If you are trying to offload work, having them on the same server will only help with resource locking (and with snapshot isolation you should be able to avoid anyway). However, if this is what you are trying to do, if you are already doing a backup to disk on the server every night, restoring the backup would probably be the least-intrusive solution to your existing processes and give you the benefit of testing your backup daily. You could also use log-shipping to get some built-in tools for this type of solution.

    If you do a restore operation, make sure you have a way to ensure nothing is connected to the database or you will have someone leave Management Studio open on their desktop overnight and break your reporting solution.