Solved

SQL server backup full with over 120 incrementals, does each backup have to be restored?

Posted on 2014-11-13
9
157 Views
Last Modified: 2016-10-27
Have a SQL database restored all is well, problem is it restored to the full backup point and there are over 100 incremental backups made to the full backup. I believe each one has to be restore hoping I'm wrong or there is a way to skip and restore the latest incremental back to the database.
0
Comment
Question by:WORKS2011
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 100 total points
ID: 40440461
Do you have any differential backups? If so, restore the latest one of those and then only the incremental backups thereafter.

Otherwise, you have over 100 backups to restore - sorry!
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 100 total points
ID: 40440782
Unless something has changed in the latest version of SQL Server (doesn't look like it according to the docs), there is no such thing as an "Incremental" backup for SQL Server.

There is a Full Database Backup.
There is a Differential Database Backup (all changes since last full backup)
There is a Transaction Log Backup

In the case you describe above, you should be seeing your 120 backups slowing growing in size as the changes keep coming after that full.

So, you should be able to do the Full, the most recent Differential and then any Transaction Logs to bring it up to as recent as possible.

Books Online can be helpful here too:

Backup and Restore of SQL Server Databases
Create a Differential Database Backup (SQL Server)
Differential Backups (SQL Server)
0
 
LVL 17

Author Comment

by:WORKS2011
ID: 40440853
Very good point, thank you. The Acronis backups are incremental how does this make the restore happen?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40440893
Sorry, I am not familiar with that software.  Looking at their website, they specifically mention an incremental backup for SQL Server - you may want to look at the documentation and see what it says, or see what you can look at in the restore GUI.  We use Dell LiteSpeed - I can prep a restore in the restore GUI and it will show me all the files needed in the restore - does your tool offer that functionality ?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 200 total points
ID: 40441112
Log back ups are chained and are relative to one another so you in case of log backups you must apply of them using the NORECOVERY for 99 and recovery for the 100th
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 40442062
Some applications calls Incremental backups to SQL Server Differential backups. That's wrong of course, since SQL Server doesn't has the option for Incremental backups.
I would guess that it's really differential backups and so you'll only need to restore the Full backup and the last Differential backup plus all the transaction log backups until the desired time of restore (STOP AT option).
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 200 total points
ID: 40442106
I agree with Vitor: it's al about third party proprietary terminology and incremental could be differential.  However, I don't see any third party proposing a solution ending up with 100 differential backups which would kill space hence the product itself.  We can safely assume that most third party vendors mean log backups by incremental backups.  Hence a full restore of all backups with STOP AT seems the way to go...

0> Confirm with your vendor that incremental backups=log backups.
1> If incremental=log backups, Find and Restore the full backup in NO RECOVERY mode
2> Restore all incremental/log backups using STOP AT
3> Put the database in RECOVERY

Hope this helps...
0
 
LVL 17

Author Closing Comment

by:WORKS2011
ID: 40486938
thank you
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40487167
Tip: to speed up recovery process in case of a large database, and if you have a spare box somewhere, instead of simply doing you can set up an frequent log shipping process to the spare box.   If loosing a box you can redirect your queries without having to restore anything. (except putting the recover database in RECOVERY mode).  Simply set up an alias on the Active Directory for the DNS and redirect it on will.

Hope this helps.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now