June 3, 2009 at 5:42 am
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.
June 3, 2009 at 7:10 am
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
June 4, 2009 at 3:59 pm
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.
---------------------------------------------------------------------
June 4, 2009 at 4:28 pm
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.
June 5, 2009 at 4:36 am
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.
---------------------------------------------------------------------
October 28, 2012 at 10:09 am
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.
October 28, 2012 at 3:11 pm
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" 😉
October 28, 2012 at 3:26 pm
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
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[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply