SQL Server Backup – How to Restore a Differential Backup in SQL Server 2005

backuprestoresql serversql-server-2005

I have created a full back up of my database by executing the line below in my application:

Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'"

The *.bak file created successfully.

Then I restored it on another Database called test and it was ok. Then I created a differential backup by means of the following code:

Dim Query As String = "BACKUP DATABASE Ari TO DISK='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'" & "WITH DIFFERENTIAL"

and it created a *.bak file ,but when I try to restore it on the database I mentioned as test ,I receive the following error:
enter image description here
Please help me solve this problem.

Best Answer

When restoring a sequence of backups (differential & logs) you specify NORECOVERY. This leaves the database in a state that will accept further restores, rather than recover it ready for use.

RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_FULL.bak' WITH NORECOVERY;
RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_DIFF.bak' WITH NORECOVERY;

-- Repeat following for each transaction log to restore if applicable
RESTORE LOG MyDb FROM DISK = '<path>\MyDb_201303050900_LOG.bak' WITH NORECOVERY;

RESTORE DATABASE MyDb WITH RECOVERY;
Related Question