Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have Database (D1) as a primary database and Log shipping setup to the secondary database called (LS_D1). Now what we are doing is taking backup of D1 database and restore it on D2 database. We do schema and data changes for new Release deployment on to D2 database.

What we want is to restore D2 database on D1 database and resume the log shipping to the LS_D1 database without copying backup file. Copying a backup file from primary data center to DR data center cost us too much money for every Release so that's why we want to resume the Log shipping.

When i tried to do this LSN numbers got out of sync, is there any way that we can solve this problem ? or can we have any other alternative to Log Shipping that can provide us DR capability and solve this problem ?

Note: Any solution that can require one time cost of log shipping would also be fine.

Thanks, Piyush Patel

share|improve this question

migrated from stackoverflow.com Mar 21 '13 at 4:46

This question came from our site for professional and enthusiast programmers.

2 Answers 2

When you restored the primary the log shipping has broken. The only way to resume it is to re-initiate it, with a new full backup. End of discussion.

We do schema and data changes for new Release deployment on to D2 database.

And that is your real problem. Do all changes as code changes and deploy by running deployment scripts. When you want to migrate the changes done in development on D2 to D1 simply run the migration scripts on D1. Use Visual Studio Database Projects for instance.

Even w/o proper development process you could had applied the changes between D2 and D1 as scripts run on D1, and don't break the log shipping relation. Eg. tools like Red Gate SQL Compare and Data Compare would had done the job.

share|improve this answer
    
The problem we are facing is we want to deploy the Release within 8 hours, and we do have some data patches to be execute while deployment which takes more than 8 hours. so to overcome this problem we are creating D2 database a week before and do our Release deployment on it mean while production is running on D1 database. Night before go Live, we restore D2 to D1 or we can point our production directly to the D2 itself. –  Piyush Patel Mar 21 '13 at 7:01
    
Make the data patches on D1, eg. use partitions and staging tables. You can't achieve what you want w/o breaking log shipping. –  Remus Rusanu Mar 21 '13 at 7:24
    
Can you elaborate more how can i use partitions and staging tables ? we wants to deploy new release within 8 hours window and only the data patches stopping us to do that. –  Piyush Patel Mar 21 '13 at 7:33
1  
Please open a separate questions. –  Remus Rusanu Mar 21 '13 at 7:51
    
What @PiyushPatel is talking can be achieved. Please see below solution that I posted. It is being PROD tested on our environment where we have to failover and failback 50-60 databases at one shot. Obviously, we have partially automated the solution, but proper testing will be required to understand the bits and pieces. –  Kin Mar 21 '13 at 15:29

We have faced the situation that you are facing currently and got creative with logshipping as there is no pause button for Log shipping.

We have implemented similar solution as a part of our Disaster Recovery fail-over testing -

Note: Below will not be clear as this site does not have any functionality for inserting a table from excel. But if you put below sequence in excel with header primary server and secondary server, it will be more comprehensible....

MAKE SURE THAT YOU TEST THIS BEFORE PUTTING IT IN PROD -- AS THE POST IS AS IS WITHOUT ANY RESPONSIBILITY OF DATA LOSS !!

ON Primary Server:

1) disable Log shipping (LS) job - Backup

2) Run LS Backup jobs manually

4) Kill all connections to database

5) Backup log(s) with norecovery (using T-SQL) - backup log db_name to disk = '\share\LogShip\db_name\db_name_YYYYMMDDHHMMSS.trn' with norecovery with norecovery

12) Restore log(s) with recovery (Using T-SQL, Log backups that were created on step 10) - restore log db_name from disk = '\share\LogShip\db_name\db_name_YYYYMMDDHHMMSS.trn' with recovery

13) Run LS Backups jobs on Primary Server

15) Enable SQL Logins and sync them

16) Enable LS jobs

ON secondary Server:

3) Run LS Copy and Restore jobs manually to make sure all the t-logs have been applied to Secondary database.

6) Run LS Copy jobs

7)Restore database(s) with recovery (Using T-SQL, Log backups that were created on step 5) - restore log db_name from disk = '\share\LogShip\db_name\db_name_YYYYMMDDHHMMSS.trn' with recovery

8) Sync logins

AT THIS POINT DATABASES HAVE BEEN RECOVERED AND CAN BE ACCESSED

Now fail back --- after step 8

9) Kill all connections

10) Backup log with norecovery (using T-SQL) - backup log db_name to disk = '\share\LogShip\db_name\db_name_YYYYMMDDHHMMSS.trn' with norecovery

11) Manually copy TRN file(s) to Primary Server (Created in step 10)

14) Run LS Copy and Restore jobs on secondary (check log shipping report)

15) Enable SQL Logins

16) Enable LS jobs

share|improve this answer
    
,what i have understood is you are changing the secondary database and then somehow you are resuming the log shipping between Primary and secondary database. our scenario is we have log shipping running between D1 and LS_D1 database, a week before go Live we restore D1 to D2 and run new deployment on D2. night before go live we will restore D2 on D1 and wants to resume Log shipping to LS_D1. What we are trying to achieve is minimum outage on database while deploying new release (schema changes, and BIG Data Patches) –  Piyush Patel Mar 22 '13 at 3:23
    
@PiyushPatel: you have to understand that even if you are able to get the solution to work, the 'BIG Data Patches' will translate into 'BIG Logs' which will have to be shipped to LS_D1 at the cost you want to avoid. –  Remus Rusanu Mar 22 '13 at 7:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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