Sunday, February 19, 2012

How to do complete DB backup using a warm standby server

I've set up log shipping between a production server and a secondary server, all work fine. Now I want to use the secondary server or the warm standby server to do complete database backup every night, since I want to off load the backup from the producti
on server, however, I get this error message for the DB backup job:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'aspnetforums' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is comple
ted.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
"
My settings are:
the restore job runs from 10:15am till 8:45 am next day, then the DB backup job starts at 9:10am. So the two jobs are not overlapping each other. However, even if I manually run the back up job at times after 9:10am, it fails with the same error message.
I am wondering if there is any extra setting I need to set to make it work.
Thank you for any help
Yi,
you'd need to Recover your database on the standby server for this to work,
which then prevents the logshipping chain from continuing. If you are sure
there is no access to the database during this period, you could Recover the
standby database then back it up, drop the logshipping maintenance plan,
then readd logshipping with the option that the initial backup has been made
and transferred.
HTH,
Paul Ibison

No comments:

Post a Comment