Can I restore only the LOG of a database?

Question

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?

Answer 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

Answer 2

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).