Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Suspend log shipping restore for full backup on standby Expand / Collapse
Author
Message
Posted Wednesday, June 3, 2009 5:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:46 PM
Points: 596, Visits: 1,691
You can't run a backup on a log-shipping standby database normally because it's either in no recovery or standby. To reduce the performance hit on our primary server that results from a full backup running for hours, we want to try backing up the standby copy instead.

What I thought might work, without breaking log shipping, is to script out a job that does the following;
1) disable log ship alert on standby
2) disable log ship restore job on standby
3) run restore database mydatabase with recovery
4) run full backup or differential backup
5. restore database with no recovery or standby
6. re-enable jobs
I'm not sure about the part where you resume restoring t log backups following a full backup, but will run a test.



Post #728064
Posted Wednesday, June 3, 2009 7:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:46 PM
Points: 596, Visits: 1,691
In test, this is working up to the point of putting the database back in standby or no recovery mode.

Msg 3153, Level 16, State 1, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Tried restoring oldest t log backup not yet restored and got this

restore LOG pst from disk='F:\MSSQL\LOGSHIPBACKUPS\pst_20090603121501.trn' WITH NORECOVERY

Error:The log or differential backup cannot be restored because no files are ready to rollforward



Post #728149
Posted Thursday, June 4, 2009 3:59 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 5,872, Visits: 12,978
Can you take the hit of the full backup at weekends only and then go for differential backups during the week, or filegroup backups if you have more than one filegroup.

---------------------------------------------------------------------

Post #729323
Posted Thursday, June 4, 2009 4:28 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:46 PM
Points: 596, Visits: 1,691
Yes I'm testing differential backups now, looking to run the full backup only on Sunday with nightly differentials. Log shipping with every 15 minute T log backups continues uninterrupted by the differentials.

Another DR possibility is stopping the sql service on the log ship standby long enough to copy the data and ldf files elsewhere for attachment. there is a kluge that allows you to do this.

Attaching a log shipped data file generates an error, for which there is a workaround. I tested it and it worked on a single mdf database.
1. Create a new database with the same name as the one you are trying to attach.
2. (optional) Detach the database, and then move the MDF and LDF files to the specific drives that you need.
3. (optional) Reattach the database from it's new location. You will have to update the paths to the LDF file when restoring.
4. Take the database Offline.
5. Copy your original MDF and LDF files and overwrite the newly created files.
6. Bring the database back Online.



Post #729329
Posted Friday, June 5, 2009 4:36 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 5,872, Visits: 12,978
sounds like you are tying yourself in knots trying to acheive this, which is always risky.

This might be one of those cases where looking into a third party tool which can do the backups faster and use compression could help.


---------------------------------------------------------------------

Post #729543
Posted Sunday, October 28, 2012 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 10:45 AM
Points: 1, Visits: 179
Indianrock (6/3/2009)
In test, this is working up to the point of putting the database back in standby or no recovery mode.

Msg 3153, Level 16, State 1, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Tried restoring oldest t log backup not yet restored and got this

restore LOG pst from disk='F:\MSSQL\LOGSHIPBACKUPS\pst_20090603121501.trn' WITH NORECOVERY

Error:The log or differential backup cannot be restored because no files are ready to rollforward



After the restore database with no recovery mode, you cannot bring back to that state again.
As restore database with no recovery mode is a transaction, newly created Log Sequence number(LSN).
after you take backup from new shinny recovery database and try to restore it again with no recovery mode, then Logshipping try to restore same start number LSN but different end LSN.
You cannot recovery and backup logshipping destination database hoping that it not break logshipping.
Post #1378035
Posted Sunday, October 28, 2012 3:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 6,420, Visits: 13,810
Indianrock (6/4/2009)
Another DR possibility is stopping the sql service on the log ship standby long enough to copy the data and ldf files elsewhere for attachment. there is a kluge that allows you to do this.

Attaching a log shipped data file generates an error, for which there is a workaround. I tested it and it worked on a single mdf database.
1. Create a new database with the same name as the one you are trying to attach.
2. (optional) Detach the database, and then move the MDF and LDF files to the specific drives that you need.
3. (optional) Reattach the database from it's new location. You will have to update the paths to the LDF file when restoring.
4. Take the database Offline.
5. Copy your original MDF and LDF files and overwrite the newly created files.
6. Bring the database back Online.

A word of caution, don't base your DR around some half baked scheme that "seems to work", you could come unstuck when you least want it and could well end up typing your resume when a disaster occurs


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1378058
Posted Sunday, October 28, 2012 3:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 6,420, Visits: 13,810
Indianrock (6/3/2009)
You can't run a backup on a log-shipping standby database normally because it's either in no recovery or standby. To reduce the performance hit on our primary server that results from a full backup running for hours, we want to try backing up the standby copy instead.

What I thought might work, without breaking log shipping, is to script out a job that does the following;
1) disable log ship alert on standby
2) disable log ship restore job on standby
3) run restore database mydatabase with recovery
4) run full backup or differential backup
5. restore database with no recovery or standby
6. re-enable jobs
I'm not sure about the part where you resume restoring t log backups following a full backup, but will run a test.

What you're describing, requires the process of a log shipping role switch which will leave the former primary database inaccessible. The switch takes the form


  • disable log ship jobs on primary and secondary

  • run BACKUP LOG mydb TO DISK = 'some\drive\path\andfilename.trn' WITH NORECOVERY. This will backup the tail of the active log and put the primary into restoring mode

  • Restore tail log backup to secondary WITH RECOVERY which will bring the secondary database online.



I'm pretty sure this is not what you want to do though, if you have high backup times consider a 3rd party tool or native compression (if avail). Also, as mentioned, consider a new regime incorporating differential backups. Full backups once a week and diffs through the week will make it easier to resume a broken log shipping scenario using a differential backup instead of a full re init. See my article on this at this link


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1378059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse