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.
-
Are you using something like LIteSpeed or did you roll your own?Steve Mangiameli– Steve Mangiameli2016年10月27日 13:31:04 +00:00Commented Oct 27, 2016 at 13:31
-
Just using the native SQL Server Transaction Log Shipping methodjdids– jdids2016年10月27日 15:23:37 +00:00Commented Oct 27, 2016 at 15:23
-
Do you really need your test environment to match PROD every minute of the day? You would probably be better server to just do a daily backup in the AM where you can actually rename your DB.Steve Mangiameli– Steve Mangiameli2016年10月27日 16:56:47 +00:00Commented Oct 27, 2016 at 16:56
-
It's going to ship logs every hour. We need this info more current and I don't want people reporting off of the live database.jdids– jdids2016年10月27日 20:12:54 +00:00Commented Oct 27, 2016 at 20:12
-
1How can you have a test server and a reporting server combined and expect to report accurate data?SS_DBA– SS_DBA2016年10月28日 13:54:10 +00:00Commented Oct 28, 2016 at 13:54
1 Answer 1
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.