Return to 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.

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.

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.

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.

I still think that 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.

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.

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.

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, 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 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.

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.

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.

I still think that 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.