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.

Improve this question
6
  • Are you using something like LIteSpeed or did you roll your own?
    – Steve Mangiameli
    Commented Oct 27, 2016 at 13:31
  • Just using the native SQL Server Transaction Log Shipping method
    – jdids
    Commented 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
    Commented 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
    Commented Oct 27, 2016 at 20:12
  • 1
    How can you have a test server and a reporting server combined and expect to report accurate data?
    – SS_DBA
    Commented Oct 28, 2016 at 13:54

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

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

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