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

Differential restore without full backup restore!! Expand / Collapse
Author
Message
Posted Saturday, July 6, 2013 1:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
HI
I have 2 servers. I do a full backup of serverA and restore it to ServerB with NORECOVERY.Now I do a differential backup of ServerA after 1 hr and Apply it to ServerB with RECOVERY. So my ServerB become replica of ServerA.
Now going forward, I just want to do differential backups of ServerA and apply them on
ServerB. To achieve this, Do I always need to do a full database restore of ServerA with NORECOVERY and then Apply differential with RECOVERY? Full backup size is big , around 50GB and it takes time to do it everytime. Is there a way just to apply the differential without first restoring the full DB?


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1470936
Posted Saturday, July 6, 2013 2:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 224, Visits: 1,732
S_Kumar_S (7/6/2013)
HI
...Do I always need to do a full database restore of ServerA with NORECOVERY and then Apply differential with RECOVERY?
Yes.

http://msdn.microsoft.com/en-us/library/ms175526.aspx
"A differential backup is based on the most recent, previous full data backup."
Post #1470941
Posted Saturday, July 6, 2013 2:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
That's bad. If nothing is changing in full backup, then it shouldn't be required to restore before each differential restore...

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1470943
Posted Saturday, July 6, 2013 2:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 807, Visits: 725
Have you considered log shipping?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1470944
Posted Saturday, July 6, 2013 3:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
but log shipping will require my database of ServerB to be in restoring mode always, isn't it? And hence I won't be able to access that.I can affford serverB to be down for few minutes, but not continously..
Erland Sommarskog (7/6/2013)
Have you considered log shipping?


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1470947
Posted Saturday, July 6, 2013 3:21 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
S_Kumar_S (7/6/2013)
but log shipping will require my database of ServerB to be in restoring mode always, isn't it?


No. You can log ship to Standby mode, which allows you to read the database. Not change it, but read it.



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 #1470948
Posted Saturday, July 6, 2013 4:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
That will be a life saver. Can it be implemented using inbuilt log shipping of sql server or I need to prepare any customized scripts?
Any link on this will be appreciated if it is some special case.

GilaMonster (7/6/2013)
S_Kumar_S (7/6/2013)
but log shipping will require my database of ServerB to be in restoring mode always, isn't it?


No. You can log ship to Standby mode, which allows you to read the database. Not change it, but read it.


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1470950
Posted Sunday, July 7, 2013 4:57 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: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
The in-built log shipping feature. As for docs, did you try Books Online?


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 #1470970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse