3

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?

Improve this question
3
  • Mirroring or AG's won't work here as you're running different versions (if they were it still wouldn't be the right solution imo). This should be scriptable, if you want it automated put it in a SQL Server Agent job with an appropriate schedule. Let us know if you need help writing that.
    – FilamentUnities
    Jun 29, 2015 at 17:41
  • you only want to restore the data and not schema? How about if you restore the entire database and just mask the sensitive data? Will that work for you?
    – Ali Razeghi - AWS
    Jun 29, 2015 at 21:56
  • ow I don't mind if the schema is restored too. There is no sensitive data to mask. I have SQL Server Agent Jobs running from Ola Hallengren which backups to .bak files weekly (full) and daily (diff). So there is no UI option...
    – JP Hellemons
    Jun 30, 2015 at 7:42

1 Answer 1

Reset to default
1

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.

Improve this answer
4
  • Thank you, but it seems like an overkill for my problem. I just want to mirror/copy a complete database from one microsoft sql server to another, or if it is easier, to the same host as different named database.
    – JP Hellemons
    Jun 30, 2015 at 7:46
  • So, did you find a solution for your issue?
    – RLF
    Jul 10, 2015 at 22:56
  • What is this linked server you talked about here dba.stackexchange.com/a/91224/4209 is that something useful for me? or do you advice to use openquery
    – JP Hellemons
    Jul 13, 2015 at 6:33
  • @JPHellemons - Updated the post to respond to your question.
    – RLF
    Jul 14, 2015 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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