CODEWITHSUNDEEP
t-sqlsql-server-2014database-restore
Liquid
Liquid

Reputation: 648

Restore differential backup SQL Server 2014

I have full, differential and transactional backups of the database. I was trying to restore one by one; but only the full backup is getting restored, but after that when I am trying to restore the differential backup facing issue with SQL Server Management Studio. So I tried with some SQL commands, this is the link what I tried with to restore, but no luck

Restore differential backup

Can anyone tell me the steps tor restore these backups? Thank you

Upvotes: 1

Views: 10901

Answers (1)

AlexMelw
AlexMelw

Reputation: 2624

It is important to be acquainted with the restore sequence of how a full database backup is restored.

First, restore full database backup, differential database backup and all transaction log backups WITH NORECOVERY option. After that, bring back database online using WITH RECOVERY option.

Following is a sample Restore Sequence

RESTORE DATABASE FROM full_database_backup WITH NORECOVERY;
GO
RESTORE DATABASE FROM differential_backup WITH NORECOVERY;
GO
RESTORE LOG FROM log_backup WITH NORECOVERY;
GO
-- Repeat this until you restore last log backup
RESTORE DATABASE WITH RECOVERY;
GO

Note:

While performing a RESTORE operation using more than one file, always use the NORECOVERY flag. This will keep the database offline to prevent any changes which could create some integrity issues. Once all the backup files have been restored, run the RESTORE command with the RECOVERY option to get the database online and operational.

Source: URL

Upvotes: 4

Related Questions