-1

We have a 1.5TB database. I could create some replication strategies do replicate the database to another server in a fixed time.But the developers don't know when they will need to replicate this development database to the teste environment.

So, i have 1TB database in the development server.I will ( I already did it ) full backup the database, and restore it in the test server. then, I would like to restore only the modifications ( because to restore everytime the full database will take a LOT of time ).

I'm trying to make a little test with 2 databases:

I created 1 database, did a full backup, and restored it. but when i try to only restore the log, I receive this error:

restore log banco_2 from disk ='e:\backup\banco1_primeiroLog.bkp' with recovery

Msg 3117, Level 16, State 1, Line 13 The log or differential backup cannot be restored because no files are ready to rollforward. Msg 3013, Level 16, State 1, Line 13 RESTORE LOG is terminating abnormally.

Should I use diff backups for this task? What would be the best strategy to this? If the question is confuse I can fix it.

I'm trying to follow this question's answer but its not working :

Is it possible to restore database only with .trn files?

  • 1
    No, there is no way to restore just logs. You need to restore a full database, then apply differential backups and/or transaction logs. If you have a common need for showing only changes in a separate database, you could consider using triggers or Change Data Capture or Change Tracking to help facilitate this. – Aaron Bertrand May 23 '16 at 14:10
  • Hey @AaronBertrand. yes there's no need to "show" what was updates. I tought I could only migrate the modifications. but, thanks for the help, I will use full backups. I will truncate some useless tables to make the database smaller. – Racer SQL May 23 '16 at 14:14
1

You need to use WITH NORECOVERY at the end of each restore until the very last one. The database was not in the right state to continue restoring backups. You get that error because you didn't do this.

With LOG backups you'd probably need to restore more than just one, you need all of the LOG backups taken since your last FULL backup. You'd restore each one WITH NORECOVERY, and at the very end just do a RESTORE DATABASE [DatabaseName] WITH RECOVERY to bring the database online.

DIFF backups have all changed pages since the last FULL backup (and so are always tied to a certain FULL backup). It's usually a quicker way to get you up to date than restoring a lot of LOG backups.

You can combine restores of these backups as long as you follow the order - full, diff, log, and so long as they all overlap in some way. And if you mess one up (like trying to restore a diff, and then an older diff, or older log), it just gets skipped and you can continue on.

Example:

Restore Database [DatabaseName] From Disk = '... full backup ...' With Norecovery
Restore Database [DatabaseName] From Disk = '... diff backup ...' With Norecovery
-- This will be ignored
Restore Database [DatabaseName] From Disk = '... an older diff backup ...' With Norecovery 
-- This will be used
Restore Database [DatabaseName] From Disk = '... later diff backup ...'  With Norecovery
-- If this was before that newest diff, it will be ignored
Restore Database [DatabaseName] From Disk = '... log backup 1 ...' With Norecovery
-- Parts of this after the newest diff, will be used
Restore Database [DatabaseName] From Disk = '... log backup 2 ...' With Norecovery
Restore Database [DatabaseName] From Disk = '... every later log backup up to the end of what you want ...' With Norecovery
-- You're finished, set the database online.
Restore Database [DatabaseName] With Recovery

Tim Radney did a video session for the PASS DR VC that might be helpful for you in understanding backup/restore basics https://www.youtube.com/watch?v=0GCZ6sNoC2o

  • Hey @cody. I'm doing this : A full backup of the database_1, a restore in the test server with a database_2 name. Then I insert some data in the database_1. I make a backup log...... In the database_1, i'm using restore log banco_2 from disk ='e:\banco_1_log.bkp' with norecovery. It's not working either. – Racer SQL May 23 '16 at 14:02
  • You need to use WITH NORECOVERY when you restore the FULL backup and EVERY backup after (until you reach the end of what you want to restore). – Cody Konior May 23 '16 at 14:05
  • Yes, I know this. I think i'm not being clear. I want to restore only the log backup. It will not have full backups. There will be modifications during the week in the Dev database, and I want to migrate it to the Test database without full backups, only with a log backup. So I will update the test database with the modifications in the development. – Racer SQL May 23 '16 at 14:08
  • Day 0: Restore full backup with no recovery. Day 10: Restore every log backup between your original backup and today with no recovery OR one DIFF backup from your main database (assuming there have been no further FULL backups taken) or a combination thereof. Then "restore with recovery" and the database will come online with the Day 10 version. However... you can only do this once. Once you restore with recovery, which makes the database accessible, it can't be restored to again without repeating the entire process from the beginning. – Cody Konior May 23 '16 at 14:11
  • The problem is, they will be using the test database. Users will be accessing the database and using it like it was production. So i can't use no recovery. AS I said to Aaron I will delete information from tables. The DB has like 15 tables but with millions of rows each. but thanks to the answer for sure I will use it in another situation. – Racer SQL May 23 '16 at 14:21
0

So, i have 1TB database in the development server.I will ( I already did it ) full backup the database, and restore it in the test server.

Restore the full backup with NORECOVERY, so that additional log backups can be applied.

Then, I would like to restore only the modifications ( because to restore every time the full database will take a LOT of time ).

You can restore log ... with standby. There are tradeoffs that you have to do when you go with standby mode :

  • All users have to be kicked out when you do a restore of log.
  • Primary and secondary has to be same version.
  • Automatic creation of stats is not available in standby mode.
  • Since you are on SQL Server 2014, you can grant connect any database and user securables to the server role, so that users can access the standby database.
  • In Standby mode, a partial recovery is done using the transaction undo file (*.TUF file) so that the database can be in a read-only state. This can be slow and requires resources thereby slowing down the restores.

Be careful, as if you loose a log file or some one takes an adhoc log file backup (you can deny log backup - to avoid this), you will end up doing a full restore (1.5TB database) or you could catchup with a differential backup (provided a full backup has not occurred).

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.