Complete or Full Backup/Restore

• Creating Backup

BACKUP DATABASE [database_name]
TO DISK = 'path\backup_filename_full.bkp'
WITH INIT

• Restoring Backup

RESTORE DATABASE [existing_database_name]
FROM DISK = 'path\backup_filename_full.bkp'

RESTORE DATABASE [new_database_name]
FROM DISK ='path\backup_filename_full.bkp'
WITH MOVE 'database_name TO 'path\new_database_name.mdf'
         MOVE 'database_name_log' TO 'path\new_datanase_name.ldf'
 

Differential Backup/Restore

• Creating Backup

BACKUP DATABASE [database_name]
TO DISK ='path\backup_filename_diff.bkp'
WITH INIT, DIFFERENTIAL

• Restoring Backup

As we know the Differential contains only the modified since last full backup, so we have to RESTORE the full backup first with NORECOVERY and then differential with RECOVERY statement.

RESTORE DATABASE [database_name]
FROM DISK 'path\backup_filename.bkp'
WITH NORECOVERY

RESTORE DATABASE [database_name]
FROM DISK 'path\backup_filename_diff.bkp'
WITH RECOVERY