SQL Server – How to Mirror DB from Production to Test and Acceptance

mirroringsql server

I have read this Easy or Schedulable way to move data from Production to Test
and it seems not automated. I would like to have a daily or weekly push of my production server data to one or two (remote) test databases.

My current production DB:

Microsoft SQL Server 2008 (SP3) – 10.0.5520.0 (X64)
Jul 11 2014 16:11:50
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM)

It might get an update to Azure SQL in the future, but it is mssql 2k8 for now.
The current test DB:

Microsoft SQL Server 2008 R2 (SP2) – 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 (Build 9200: )

I have a lot of sql server jobs from http://ola.hallengren.com and I know about the existence of the import/export wizard, but I'd like to have an easy to setup weekly automated solution to export the data (logs not required) to my test and/or acceptance DB (located on other server)
So I don't think that I need a clustor or failover stuff, just a copy of the data to an other server in an easy automated way.

edit I have read that 2012 gives availability groups, but isn't that an overkill for my situation?

As I already have weekly full backup .bak and daily diff .bak files on my production server, I might just automated copy this somehow an restore. Is this a good idea? https://serverfault.com/a/472007/30695 or is there a microsoft tool with a nice wizard for it?

Best Answer

It is true that Availability Groups can make replicas of the active database and can support (depending on version) one or two readable replicas. This does not work with SQL Server 2008 R2 servers. But even if it did, this is not really the vehicle (yes, it is overkill) for getting a couple of databases to test with.

Using the backups that you are already taking to restore a database to another server is the quickest and easiest way to get a database to test against.

If you use mirroring, log shipping, or Availability Groups you can make something work within the parameters of those technologies. All three of these approaches require terminating those processes in order to do tests that include any updates to the data.

So, check this discussion for some details on using backup/restore and a useful script to help you:

Automatically restore SQL Server database from file from another server

EDIT: Getting a copy of a database to another server or using a new database name on the same server are most easily done by restoring the database from existing backups. (Provided that your backups are frequent enough to support your tasks.)

  1. If you restore to the same server but using a different database name, the logins are already on that server, so no extra work is needed. Likewise, the linked server is not needed in this case, since the information in the msdb database is local to your restore.
  2. If you restore to an other server, depending on your needs you might need to add missing logins to the other server. Since you would run the restore on the other server the linked server provides the path needed for the other server to select the information from the original server's msdb database to do the restore on the other server.

The linked server in the post was intended for case 2 above, where you need to derive the restore information for the other server from the original server.

Paul Brewer's script (or your edited version of the script) will likely help you automate your process. But there are plenty of other scripts out there if you search for them.

Related Question