Differential Backups

There could be different backup methods one needs to make a careful choice of which method would suit best with one's requirement.

1. The simplest and the most widely used methods is the complete backup. In the complete backup a copy of the transaction log is also made this is used to recover the database to the last possible consistent state.

backup database BCG
to disk = 'c:\bcg.bak'

2. The second method is a file backup where one can backup one file at a time instead of backing up all the files in one go. This method is handy when the database size is very huge and backing up all the files would take too long.
BACKUP DATABASE BCG
FILE = BCG_Data_1
TO DISK = 'C:\BCG_File1.bak'

3. Diffenrential Backup creates a copy of all the changes that have taken place in the database ever since the last complete backup. This command is same as the complete backup except for one differential clause.

BACKUP DATABASE BCG to disk = 'C:\bcg_1.bak' with differential

4. The transaction log backup takes a backup of only the transaction log file once the transaction log is backed up it is marked for truncation and a new log is started thus when multiple file logs are being applied onto a database it is important that all the logs are available and they have to be applied in the same sequence.

BACKUP LOG BCG TO disk = 'C:\BCGLog.log'

How to restore a backup also changes depending the method used for backing up the database. The simplest of all is the full restore by default a recover mode is what is used by SQL recover mode means that SQL is done with the restore it will roll back all the incomplete transactions in the database and bring the database to a consistent state.

restore database BCGNew from disk = 'C:\bcg.bak'

In case a differential backup or transaction logs are being applied onto a backup we would not like the database to recover and roll back after the restore is done as its quite possible that the transaction was committed in the following file thus with the restore option the norecover clause is used.

backup database BCG to disk = 'c:\bcg_1.bak' with differential

BACKUP LOG BCG
TO disk = 'C:\BCGLog.log'

Assuming that between each of these commands there are changes and transaction that have been committed into the database. What is expected is that when the new database is restored all these changes are available. The sequence to recover the data to the last possible state would be as follows:

restore database BCGNew
from disk = 'C:\bcg.bak'
with norecovery
, move 'BCGWDCM_GLC_Data' to 'C:\BCGNew_data'
, move 'BCG_data_1' to 'C:\BCG_data_1'
, move 'BCGWDCM_GLC_Log' to 'C:\BCGWDCM_GLC_Log'

restore database BCGNew
from disk = 'C:\bcg_1.bak'
with norecovery

restore log BCGnew
from disk = 'C:\BCGLog.log'

The point to be noted here is that norecover clause is being supplied with each restore option but the last. Once a restore is done without the norecover option no more restores would be allowed on the database.