Logshipping - Need full backup of Sencondary Database

Question

Logshipping - Need full backup of Sencondary Database

ravisamigo

SSCertifiable

Points: 7440
More actions
March 19, 2012 at 1:43 am

#258689

Hi,
We've SQLServer 2005 logshipping configured on one of production database and the size of this db is around 1 TB. It will take more time to take the backup of the primary database every time.
Not possible to take the backup of secondary database because it is in Restoring phase.
So, please advice how we can take full backup from secondary database or is there any alternative
solution for this.
Thanks and Regards,
Ravi.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

the only way to backup the secondary would be to restore it with recovery so that it comes online for you to back it up. this will terminate the logshipping process and you will need to reconfigure the database again by restoring the database in norecovery mode and then setting up the log backups.

i've never thought about this, but could be open to a lot of interpretation around licencing, as you are going to be executing queries against your secondary, which could break the licencing terms and means you might need to licence your secondary node.

have you thought about your backup routines? considered doing diffs instead of fulls?

also do you have enterprise edition of SQL 2005? Could you not configure backup compression to speed up the backup process?

ravisamigo SSCertifiable Points: 7440 More actions · Answer 2

Thanks for your reply.

Will log chain be break/logshipping get failed if we take differential backup of the primary db ?

Thanks and Regards,

Ravi.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

no it wont as your not affecting the transaction log

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

ravisamigo (3/19/2012)
Hi,
We've SQLServer 2005 logshipping configured on one of production database and the size of this db is around 1 TB. It will take more time to take the backup of the primary database every time.
Not possible to take the backup of secondary database because it is in Restoring phase.
So, please advice how we can take full backup from secondary database or is there any alternative
solution for this.
Thanks and Regards,
Ravi.

logically i dont think you need to have backup of secondary side as primary's database backup will work better and more trust worthy with respect to the data availability.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)