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!!
AFTERthat 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.