I am not a DBA nor a database guy, we just recieved a database backup, a differential database backup and daily database transaction logs (each day probably has about 15-20 trans logs). I now have about 20 days worth of transaction logs.

In case the database crashes, I do not want to re-install the original database, the original differiential backup and XXX days of transaction logs.

My question is: Can I just restore the base database backup, the differential database backup, the 1st 10 days of transaction logs and then do a backup of the database, and then still use the 11th day of the transaction logs to add to the NEW backup I just created? How would I go about doing this? Could someone provide me some T-SQL code on how this could be done or is there a much easier way to work around this?

Well actually I already have some of the steps done, but confused on what would be the best solution since my new database does not seem to work with the trans logs, below is my code: Restores base backup

RESTORE DATABASE db_wasumu FROM DISK= 'C:\Summit_FTP\Full_Backup\ECW33DBCUSTR12A_db_wasumu_FULL_20160522_060256.bak' WITH MOVE 'db_wasumu' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu.mdf', MOVE 'db_wasumu_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu_log.ldf', REPLACE, NORECOVERY, STATS = 10;

Restores diff backup

RESTORE DATABASE db_wasumu FROM DISK= 'C:\Summit_FTP\Full_Backup\ECW33DBCUSTR12A_db_wasumu_DIFF_20160527_004940.bak' WITH MOVE 'db_wasumu' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu.mdf', MOVE 'db_wasumu_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu_log.ldf', REPLACE, NORECOVERY, STATS = 10;

Restores transaction log

RESTORE LOG db_wasumu FROM DISK = 'C:\Summit_FTP\wasumu_20160527\ECW33DBCUSTR12A_db_wasumu_LOG_20160527_010404.trn'


Create new backup

Now I do the backup: BACKUP DATABASE db_wasumu TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\db_wasumu-Full Database Backup_after0529_2.bak'

But when I tried to append the next Transaction log it states:

Msg 4330, Level 16, State 1, Line 1 This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online. Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Thanks in advance for your help!!

Sincerely, Paul

  • 2
    When you backup a database, you are essentially "resetting" the log chain and only tlogs taken AFTER that backup will be candidates for recovery. So what you are doing will not work. If you tell us what you are trying to accomplish with this scheme, we might be able to help you better. – Steve Mangiameli Jun 20 '16 at 18:56
  • since this is not our internal database (we have no control over the process), we will probably constantly be recieving daily files from them. In case of database issues, I do not want to have to restore from the base table, differential table and all the transactions logs (after a year, there will probably be over 1000 transaction logs). I want to be able to have a new starting base which will be less work. Thank you for the response, any help is greatly appreciated! -Paul – image13 Jun 20 '16 at 20:13
  • 2
    To get the latest version of the database, you would restore the latest full, the latest differential and then any transactionals AFTER the latest differential. If they take a differential every day then you would at most have perhaps 22 files to restore and there are scripts out there that can help. – Jonathan Fite Jun 20 '16 at 23:00

That won't work. As soon as you recover the database after restore, that begins a new timeline for the database.

If this is an on-going process, one possible solution is to get the team generating the backups to take a differential every day which will have fairly low impact on effort and system load. This is in addition to what they currently have. However, it will require some careful management of the files to ensure you have the right sequence of full backup, differential and tlogs. The benefit is, you will not have more than the 20 tlog files to apply if you ever need to restore the database to the latest version. You also don't lose existing PITR based on the 15-20 tlog backups taken daily (I'm assuming there is some PITR requirement given the number of log backups).

If you want to further reduce the number of log files to apply, you can increase the number of differential backups per day but I'd wager the added complexity in managing the files far outweighs the benefits. If you script all your tasks (sounds like you are), execution and scrutinizing for issues is fairly easy to do. If you're using the SSMS GUI, well, you really shouldn't.

** To make it clear, this assumes you already reset the backup chain with a full database backup at some interval. Having just a single full DB backup and only taking differentials and tlog backups from there on is not a viable solution. In most situations, the differential will eventually grow to a large size, possibly bigger than the base (full DB) backup if you have lots of write activities. An example of a basic backup cycle might look like: - Full DB backup - every Sunday 2am - Differential DB backup - every day - 8pm Transaction log backup - every 15minutes This cycle has potential data loss of up to 15 minutes if there are no other protections (e.g. AlwaysOn) in place.

If this is just a 1-time thing, i.e. you won't get any more tlog backups then you could reduce your recovery time when the goal is to get to the latest point-in-time available. Just restore the DB, differential and apply all the logs. Once done, take a backup of that and keep it along with all the other backup files you have. This buys you rapid recovery when you need to get to the latest version available since you only need to do a single DB restore. However, don't delete all the other backup files in case you need to restore to a specific point-in-time other than the most recent.

  • Thanks for the response, wouldnt it be better if I have then create and give a full backup every month? Would that be the best case senario or would differential backups be better way to go? Yes, this is an on going thing for how ever many years we stick with them. – image13 Jun 21 '16 at 17:55
  • Sure, if it's in addition to what already exists. It's another step to optimize recovery time. Many environments have full DB, differential DB and tlog backup cycles to optimize both the process and recovery time. Time split between each of them are determined by RPO and RTO requirements. Key is to restart the backup chain on a periodic interval with a full DB backup. Else you will eventually end in a management nightmare. Keep it long enough, you will run into serious issues with your RTO. Applying 240 Tlog backups can take a really long time because of the number of files to cycle through. – SQLmojoe Jun 21 '16 at 20:01
  • Thanks, yea, I will contact them to see if we can get a full backup on a monthly basis or else I am going to have over 1000 transaction logs to cyle through if this database ever crashes or gets corrupted. So it seems, this can only be done on their side, I can not create a full backup myself and use their transaction files, which is a bummer. – image13 Jun 22 '16 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.