Import Sql Server Backup and Differential

Asked

Viewed 674 times

1

I’m having a problem importing a backup to the secondary server:

The topology: Server Production >> Server contingency.

When Full Backup is imported it matters without any problem. The problem occurs when I import the Differential Backup, which has an error (msg 3117), However when I import the full backup along with the differential backup (regardless of any of them) the import takes place without any problem, however this use is not productive due to the time of import and processing of the machine.

Scripts:

/*TRANLACT-SQL*/
/*FULL*/
RESTORE DATABASE ASCCLUB  
   FROM DISK = 'D:\SQL\2017\Janeiro\23\FULL\20170123_HORA010000_FULL.bak'
   WITH REPLACE, NORECOVERY;  
/*DIFF*/
RESTORE DATABASE ASCCLUB
   FROM DISK = 'D:\SQL\2017\Janeiro\23\DIF\20170123_HORA170000_DIF.bak'
   WITH RECOVERY;

Error when importing only Dif:

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

  • You cannot make a differential backup without full backup. The name already says, it is the difference between some point. What you have to do is a log backup

  • I understand, I import the Full backup without problems, and soon after I import the Dif, in a same sql it goes without problems, however when I perform a script and the diff in sequence occurs this error.

  • Yes, what you need, from what I understand in your problem is to backup Log. This can be restored without need of a basis, but will only encompass the period that has been determined. I’ll write an answer detailing you better

  • Now I understand, I’ll wait for your most complete explanation and I’ll test.

1 answer

1


To answer your question, I will use the definition of 3 backup types available in SQL Server, the complete backup, differential and transaction log.

The full backup, as the name already says, makes a copy of all available data at the time of execution.

The differential backup copies the data that was changed from the last complete, hence the name, since it is a difference from the data since the last complete.

The log backup copies the changed data in a given period. This method serves for you to restore a bank from a certain point or time.

So for your case the log backup is more indicated.

You can use the following command to perform log backup:

BACKUP LOG ASCCLUB
TO DISK = 'D:\SQL\2017\Janeiro\23\LOG\20170123_HORA170000.TRN'
GO

And to restore:

RESTORE LOG ASCCLUB FROM 'D:\SQL\2017\Janeiro\23\LOG\20170123_HORA170000.TRN' WITH RECOVERY;  
GO

References:

Backup types in SQL Server

Transaction log backups (SQL Server)

  • I did the test, however it did not work properly, following the same problem of only importing it if you import then from the Full backup, if you import the full and after one minute it accuses the same error mentioned above.

  • Full has to be done only once with this type of backup. Then you can do as many logging vc as you want

  • I did not understand well, what spoke, in case I will necessarily need to import all the generated logs ?

  • No. You only need to import from the last one that was imported

Browser other questions tagged sql sql-server backup

You are not signed in. Login or sign up in order to post.