Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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;
share|improve this answer
    
I'm really a beginner,is it possible to use the designer to restore the database in nonrecovery mode? –  Shadi S Mar 5 '13 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 Mar 5 '13 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 Mar 6 '13 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 Mar 6 '13 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 Mar 9 '13 at 10:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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