patternsqlMinor
Restoring a differential backup in SQL server 2005
Viewed 0 times
sqlrestoring2005differentialserverbackup
Problem
I have created a full back up of my database by executing the line below in my application:
The
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:
and it created a
Please help me solve this problem.
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: Please help me solve this problem.
Solution
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 = '\MyDb_FULL.bak' WITH NORECOVERY;
RESTORE DATABASE MyDb FROM DISK = '\MyDb_DIFF.bak' WITH NORECOVERY;
-- Repeat following for each transaction log to restore if applicable
RESTORE LOG MyDb FROM DISK = '\MyDb_201303050900_LOG.bak' WITH NORECOVERY;
RESTORE DATABASE MyDb WITH RECOVERY;Code Snippets
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;Context
StackExchange Database Administrators Q#35949, answer score: 6
Revisions (0)
No revisions yet.