fullbkup + diff bk up - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

fullbkup + diff bk up

Author

Topic

steel
Posting Yak Master

107 Posts

Posted - 2008-05-25 : 10:13:34

hi all,
i want to restore a database for which i have taken a full backup and then a diff backup . how should i do that

thank u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-25 : 10:19:16

http://articles.techrepublic.com.com/5100-10878_11-1041267.html

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-25 : 10:53:43

Restore Fullback with Norecovery option(Second option in GUI)
and restore Diff backup with Recovery option(First option in GUI).

steel
Posting Yak Master

107 Posts

Posted - 2008-05-27 : 08:54:47

the actual scenario is that we are taking a full backup of the database and taking regular differential backup which is overwriting the previous file . the first time the process of restoring works fine but during second time we are getting the error

Server: Msg 3136, Level 16, State 1, Line 2
Cannot apply the backup on device 'E:\abc\logtest\abcdiff' to database 'abc'.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

the first time the process of restoration works fine but second time it fails.

please help me out.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-05-27 : 09:06:46

i suspect you'd screwed.

full backup #1 to filea
diff backup #1 to fileb
diff backup #2 to fileb

this last one should have cleared out the important information from diff backup #1.
if fileb was filec, then you probably would be o.k.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-27 : 15:40:04

It sounds like your full backup that you're restoring was taken AFTER the differential backup. Check your date/time stamps on the files. You should be able to restore a full backup with no recovery and then the latest differential backup on top of that. That's the intent of differential backups.

Terry

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 15:43:59

quote:
Originally posted by AndrewMurphy

i suspect you'd screwed.

full backup #1 to filea
diff backup #1 to fileb
diff backup #2 to fileb

this last one should have cleared out the important information from diff backup #1.
if fileb was filec, then you probably would be o.k.

This isn't true. Differentials save the data differences since the last full backup. You do not need previous differentials to restore if you have the last full backup and the most recent differential.

Transaction log backups are a different story though. You need the entire tlog chain since the full backup (or full + diff).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources