Sql-server – Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current

restoresql server

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'

WITH NORECOVERY;

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

Best Answer

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.

Related Question