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 12»»

Always on Secondry database backup plan & Recovering. Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 49, Visits: 178
Hi All,

Can any one suggest me one good backup plan for Always on secondaries server... ?Currently i am planning to do

One Full copy only backup--- Every 24 hour :

Every 30 min Log backup.

I am looking for the solution for recovering the database using this backup strategy.
Post #1428883
Posted Saturday, March 9, 2013 7:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 23,400, Visits: 32,256
Why a copy only full backup? Why not a normal full backup?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428885
Posted Saturday, March 9, 2013 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 49, Visits: 178
I am implementing the backup plan on my secondary server.....We cannot the take the normal backup from this.....

Please suggest me..
Post #1428886
Posted Saturday, March 9, 2013 7:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 23,400, Visits: 32,256
Okay, I was just curious as I haven't looked at Always On as yet. Thanks for the tip.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428887
Posted Saturday, March 9, 2013 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 49, Visits: 178
Is there any suggestion from anyone...?
Post #1428903
Posted Saturday, March 9, 2013 9:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Suggestions for what?

How you recover with the backup strategy you listed is pretty easy, restore latest full backup, restore all log backups.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1428906
Posted Saturday, March 9, 2013 10:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 49, Visits: 178
here the problem is i can schedule copy only full backup and log backup on secondary server...

Using this is it possible....?
Post #1428909
Posted Saturday, March 9, 2013 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 49, Visits: 178
Hello,

I have configured the SQL server always on with 4 server, and out of these 3 server is synchronizing real time (Database and data is secured).

But when i am implementing the Backup plan in Always on secondary server, there we cannot take the normal full backup & Differential backup..and possible only Copy only full backup and Log backup. Using these copy only full backup and log backup i am not able to recover the database.

So how to manage this scenario ?

:)
Post #1428910
Posted Saturday, March 9, 2013 10:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
ratheesh4sql (3/9/2013)
here the problem is i can schedule copy only full backup and log backup on secondary server...

Using this is it possible....?


Have you tried? If so, does it not work? If not, why exactly and with what errors?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1428911
Posted Saturday, March 9, 2013 12:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 49, Visits: 178
You mean to say that we can recover the data using copy only full backup and log backup.....but unfortunately i couldnt restore it successfully.

I tried below scenario

2 pm - Copy only Backup

2.30 pm - Log backup

3.00pm - Log backup'

i couldn't restore 2pm copy only full backup+ log backup which i have taken.

There is a LSN related error's was coming.
Post #1428923
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse