Sql-server – Possible to ship logs to database with different name, but same structure

sql serversql-server-2008

I'm struggling here. Using SQL Server 2008 Standard

Our environment has a prod SQL Server and a Test SQL Server. What I'm looking to do is take some processing power off of the production server and create a log shipped instance on the test server for reporting purposes. This database is located on both production and test. The test system is also used to test, so I can't restore the logs to the same database name as production because the database will be in a constant state of restoring or in read only mode and we need full read/write capability. I have another database in test called ProdDatabaseNameReporting that is a carbon copy of the one I'm trying to get the logs shipped from.

Here's what I'm trying to do and what I told it to do

ProdDatabaseName --------> ProdDatabaseNameReporting

Here's what is happening

ProdDatabaseName --------> ProdDatabaseName

Everything looks to be setup correctly. Then the log shipping procedure begins and it starts to overwrite the database with the same name instead of the database I told it to log ship to.

I know I can restore the database manually from production to test and it will restore correctly.

Best Answer

It's been a while since I've used the GUI, however you can do it using TSQL with the command sp_add_log_shipping_secondary

With this command you would specify the name of the secondary database and the primary, which would then restore the transaction logs to the relevant database name.

EXEC master.dbo.sp_add_log_shipping_secondary_database   
@secondary_database = N'ProdDatabaseNameReporting'   
,@primary_server = N'YourPrimaryDatabaseServer'   
,@primary_database = N'ProdDatabaseName'   
,@restore_delay = 0   
,@restore_mode = 1   
,@disconnect_users = 0   
,@restore_threshold = 45     
,@threshold_alert_enabled = 0   
,@history_retention_period = 1440 ;  
GO

Going through the full TSQL implementation of Log Shipping using TSQL from https://msdn.microsoft.com/en-us/library/ms190640.aspx would be a good place to start for you.

Related Question