3

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.

Improve this question

1 Answer 1

Reset to default
6

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;
Improve this answer
5
  • I'm really a beginner,is it possible to use the designer to restore the database in nonrecovery mode?
    – Shadi S
    Commented Mar 5, 2013 at 7:21
  • Yes, in the restore options look for 'Recovery state'. Default is 'Restore with recovery', change to 'Restore with norecovery'. This is covered in the documentation.
    – Mark Storey-Smith
    Commented Mar 5, 2013 at 8:31
  • Thanks,but when I try to restore in this mode,the restore operation continues until restarting the SQLServer service !I want to make an app for this goal,and I don't want to make the user stop and start the service !please suggest me a solution!
    – Shadi S
    Commented Mar 6, 2013 at 11:28
  • 1
    Read the documentation. The final backup that you restore you need to specify WITH RECOVERY, or issue a separate recovery command as per the last line in the script in my answer. But, are you sure this is something you should be building into an application when you don't appear to understand the fundamentals of SQL Server backups?
    – Mark Storey-Smith
    Commented Mar 6, 2013 at 11:36
  • Thanks a lot,this time my problem has been solved by your answer,but I try to study SQL server more deeply !
    – Shadi S
    Commented Mar 9, 2013 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.