March 19, 2009 at 2:10 am
Hi
i am having two agent jobs, one is for full back which runs on every Sunday and the second one for diff backup which runs for rest of the days (Mon to Sat ), these jobs are executing very well
Full Bkp Script:
===========
SET @filename = ''E:\Backup\DB_Date.bkp''
BACKUP DATABASE [DB]
TO DISK = @filename
WITH INIT, NOUNLOAD, NAME = N''DB',
NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @filename',
Diff Bkp Script:
===========
DECLARE @filename VARCHAR(255)
SET @filename = ''E:\Backup\DB_Date.bkp''
BACKUP DATABASE [DB] TO DISK = @filename WITH
DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''DB-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO'
But when i am trying to restore this DB with diff it is giving error, i have checked with both options "RESTORE WITH RECOVERY" && "RESTORE WITH NORECOVERY", and also with all Diff and full with only last diff, and restore first full then try again for a diff, all i got exceptions. here the exception
Exception
=======
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
March 19, 2009 at 2:23 am
To restore from a diff you need to restore the most recent FULL backup first, and then whichever diff backup (probably the most recent again) you need. The error you are getting is telling you that the db is not in the state it should be after restoring the most recent full backup.
Mike John
March 19, 2009 at 2:30 am
and while restoring the fullbackup use WITH NO RECOVERY and while restoring diff backup use WITH RECOVERY
March 19, 2009 at 3:49 am
You can try something like this. Restore your latest Full Backup file with a new database name using the NO RECOVERY option. After this the database should status will show as Recovery / Stand By.
Now restore the latest Diffrential Backupfile on the same database using option RECOVERY. After this you will not be able to restore any more file. You need to restore only one the latest Diff file.
Now database will be Online for the use. Hope this will help you.
March 19, 2009 at 4:04 am
Thank You all for your replies, as the users are up now, i will be trying it tomorrow, and i will update the results
Thank you once again
March 20, 2009 at 12:17 am
Sorry still i am having the same problem, i have followed the same as above, here the error i am getting
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'ENDSQLVIP102'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
March 20, 2009 at 12:31 am
It maybe due to:
1)You are not restoring the recent full backup.Check the date of the recent fullbackup.
2)Someone have performed a fullbackup of that database after you have done.So the next differential backup is related to that fullbackup and not yours.Check the logs for any recent fullbackup performed on that database.
March 20, 2009 at 9:36 am
Raju,
Can you post the restore statements that you're running so we can see exactly what you're doing?
Thanks
Greg
March 22, 2009 at 7:09 am
and also a snapshot of your errorlog file which includes any specific restore commands.
March 24, 2009 at 11:19 pm
Thank You Very much for the support and help for this issue, Today i have restored the Backup Successfully. As "SQL Reddy" said this point
1)You are not restoring the recent full backup.Check the date of the recent fullbackup.
there was other team member has taken bkp for his own purpose. thank you very much to ALL.
there is another question i have is,
My DB actual backup file size is 10GB and per day my differential bkp size will be around 3500KB or max 4MB . but when i restore ( as said above posts) my DB it is ( ldf & mdf ) taking 44GB on disk.
is this is the behavior or am i doing some thing wrong, because it is almost taking 400% than the actual size. Please help me out on this
once again thanks ALL
March 25, 2009 at 2:15 pm
View command DBCC SHRINKFILE
December 22, 2009 at 8:59 am
Hi,
could you please help me out, how can restore only the full backup from the same file which is also having differential backup
December 22, 2009 at 9:13 am
Do you mean you ran the full and backup to the exact same file name?
you should be able to select the restore dialog, then select the file as a restore from and see the full backup and diff backup in there.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 22, 2009 at 9:41 am
raju.tanneeru (12/22/2009)
Hi,could you please help me out, how can restore only the full backup from the same file which is also having differential backup
Duplicate post in an old thread. Reply to new thread here:
http://qa.sqlservercentral.com/Forums/Topic837972-357-1.aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply