Import Backup and Differential Sql Server

Navigation
1

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

The topology: Production Server > > Contingency server.

When importing Full Backup, it imports without any problems. The problem occurs when I import Differential Backup, which causes an error (msg 3117), But when importing the full backup together the differential backup (regardless of any of them) the import occurs without any problem, but this use is not productive due to the time of import and consumption of processing of the machine.

Scripts: 

/*TRANLACT-SQL*/
/*FULL*/
RESTORE DATABASE ASCCLUB  
   FROM DISK = 'D:\SQL17\Janeiro\FULL170123_HORA010000_FULL.bak'
   WITH REPLACE, NORECOVERY;  
/*DIFF*/
RESTORE DATABASE ASCCLUB
   FROM DISK = 'D:\SQL17\Janeiro\DIF170123_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.
    
asked by anonymous 23.01.2017 / 20:47

1 answer

0

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

Full backup, as the name already says, makes a copy of all available data at runtime.

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

The log backup copies the changed data over a period of time. This method is for you to restore a bank from a particular point or time.

So for your case log backup is more appropriate.

You can use the following command to perform a log backup: 

BACKUP LOG ASCCLUB
TO DISK = 'D:\SQL17\Janeiro\LOG170123_HORA170000.TRN'
GO

And to restore: 

RESTORE LOG ASCCLUB FROM 'D:\SQL17\Janeiro\LOG170123_HORA170000.TRN' WITH RECOVERY;  
GO

References:

Backup Types in SQL Server

Transaction log backups (SQL Server)

    
23.01.2017 / 21:36
How to detect the selected dataset in a charts Algorithm involving statistics