MS SQL Server 2012: once a day full replication (automatic backup/restore) for reporting Our company is w/o a DBA, and it has fallen on me (a complete DB noob) to handle some critical database tasks.

I'm looking for the best solution for us to be able to "copy" a full database to another server, once per day.  We want to handle schema changes, and data can be stale up to 24 hours.

Our current DB size is around 16 GBs, and we don't expect it to grow very fast.

What would be best?


transactional replication?  (if so, how do we schedule this to only update once per day?)
backup and restore? (what is the best way to automate this?  I would assume a differential backup / restore would be best for speed / bandwidth?)
any other method that would better fit our needs?


Thanks for your response!.
We want to handle schema changes


Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :


If not on enterprise edition, you can use any of below :


Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.
Since data can be stale up-to 24 hrs, another option would be to use logshipping. You just have to delay the restore of the log backups on the secondary server. So when you want to update the data on the reporting server, you can kickoff the job manually or it will get kicked off with the schedule. This way, you just have to restore the T-logs on the secondary server, when you want to refresh the data in the reporting (secondary) server.

So when setting up Logshipping, configure the secondary database in STANDBY mode and make sure you select Disconnect users in the database when restoring backups.

If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.


Note: For completeness of this answer, even though Database Mirroring is announced as Deprecated, it is still supported in SQL Server 2012. You have to be careful, as the database snapshot name will be different from the original database name, so if your app relies on the database name, it will need modification. Also, as snapshot works on "COPY ON WRITE" method, it can be significant overhead if there are multiple snapshots. Also, it will randomizes IO during query processing.