0

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 Oct 27 '16 at 13:31
  • Just using the native SQL Server Transaction Log Shipping method – jdids Oct 27 '16 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 Oct 27 '16 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 Oct 27 '16 at 20:12
  • 1
    How can you have a test server and a reporting server combined and expect to report accurate data? – WEI_DBA Oct 28 '16 at 13:54
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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