Restoring differential backup - 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 2005 Forums

SQL Server Administration (2005)

Restoring differential backup

Author

Topic

honey_191
Starting Member

49 Posts

Posted - 2008-02-09 : 14:26:36

Hi,
Im trying to restore the differential backup,I have to restore the full backup first and then restore the differential backup.Can any body tell me the code for this?

my program is like this
--for fullbackup
if type=D
begin
restore database databasename
from disk=path
end
--for differential backup
else if type=I
???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-09 : 14:33:25

Use WITH DIFFERENTIAL in the RESTORE command to restore a differential backup. See BOL for details.

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

honey_191
Starting Member

49 Posts

Posted - 2008-02-09 : 15:06:22

Do i need to restore the full backup once again?
if i need to restore the full backup & differential backup

my code is like this,is any thing wrong in my code

alter procedure res1
as

declare @db as varchar(100)
declare @path as varchar(100)
declare @type as char(5)
declare @bfd varchar(100)

set @bfd=(select max(backup_finish_date) from resto)

set @path=(select physical_device_name from resto where backup_finish_date=@bfd)

set @db=(select database_name from resto where backup_finish_date=@bfd)

set @type=(select typ from resto where backup_finish_date=@bfd)

if @type='D'
begin
restore database @db
from disk=@path
with stats=10,recovery
end

else if @type='I'
begin
restore database @db --(i need to resotre the fullback here,is this correct????)
from disk=@path
with norecovery

restore database @db
from disk=@path
with stats=10,recovery
end

else if @type='L'
begin

restore database @db --(i need to resotre the fullback here,is this correct????)

from disk=@path
with norecovery

restore log @db
from disk=@path
with stats=10,recovery
end

else
print'operation failed'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-09 : 15:32:33

You restore the full backup only once, but you must specify WITH NORECOVERY in order to apply anything after that.

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

honey_191
Starting Member

49 Posts

Posted - 2008-02-09 : 16:36:30

If you don't mind,can you tell me whats wrong in my program?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-09 : 17:39:21

You aren't using WITH DIFFERNTIAL for the differential restore.

Rather than editing your first post with our suggestions, please submit new replies with the modified code so that our posts don't look so odd. Your first post did not originally have the WITH NORECOVERY, so my second post looks incorrect even though at the time it wasn't.

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

honey_191
Starting Member

49 Posts

Posted - 2008-02-09 : 17:59:03

WITH DIFFERENTIAL is used only for backup,For restore also can we use that "WITH DIFFERENTIAL"????

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-09 : 19:19:28

No you can't.

honey_191
Starting Member

49 Posts

Posted - 2008-02-09 : 19:25:34

thnx rmiao,
Is there anything wrong in my program???
alter procedure res1
as

declare @db as varchar(100)
declare @path as varchar(100)
declare @type as char(5)
declare @bfd varchar(100)

set @bfd=(select max(backup_finish_date) from resto)

set @path=(select physical_device_name from resto where backup_finish_date=@bfd)

set @db=(select database_name from resto where backup_finish_date=@bfd)

set @type=(select typ from resto where backup_finish_date=@bfd)

if @type='D'
begin
restore database @db
from disk=@path
with stats=10,recovery,replace
end

else if @type='I'
begin
restore database @db
from disk=@path
with norecovery,replace

restore database @db
from disk=@path
with stats=10,recovery,replace
end

else if @type='L'
begin

restore database @db
from disk=@path
with norecovery,replace

restore log @db
from disk=@path
with stats=10,norecovery,replace

restore log @db
from disk=@path
with stats=10,recovery,replace
end

else
print'operation failed'

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-09 : 22:43:38

Does it work? Got any error when run it?

honey_191
Starting Member

49 Posts

Posted - 2008-02-09 : 23:12:27

Thanks alot to all of u for the help.I got the result finally..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-10 : 20:51:17

Sorry about misleading you about the WITH DIFFERENTIAL. I was thinking we were talking BACKUP command even though your post says RESTORE. I didn't read it close enough.

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

honey_191
Starting Member

49 Posts

Posted - 2008-02-11 : 03:07:54

It's ok Tara mam.I am getting only the full backup,I am unable to restore the Differential and log backup.Can you tell me anything wrong in my code???

alter procedure res1
as

declare @db as varchar(100)
declare @path as varchar(100)
declare @type as char(5)
declare @bfd datetime

declare @db1 as varchar(100)
declare @path1 as varchar(100)
declare @type1 as char(5)
declare @bfd1 datetime

declare @db2 as varchar(100)
declare @path2 as varchar(100)
declare @type2 as char(5)
declare @bfd2 datetime

set @bfd=(select max(backup_finish_date) from resto where typ='D')

set @path=(select physical_device_name from resto where backup_finish_date=@bfd)

set @db=(select database_name from resto where backup_finish_date=@bfd)

set @type=(select typ from resto where backup_finish_date=@bfd)

set @bfd1=(select max(backup_finish_date) from resto where typ='I')

set @path1=(select physical_device_name from resto where backup_finish_date=@bfd1)

set @db1=(select database_name from resto where backup_finish_date=@bfd1)

set @type1=(select typ from resto where backup_finish_date=@bfd1)

set @bfd2=(select max(backup_finish_date) from resto where typ='L')

set @path2=(select physical_device_name from resto where backup_finish_date=@bfd2)

set @db2=(select database_name from resto where backup_finish_date=@bfd2)

set @type2=(select typ from resto where backup_finish_date=@bfd2)

if @type='D'
begin
restore database @db
from disk=@path
with stats=10,recovery,replace
end

else if @type1='I'
begin
restore database @db
from disk=@path
with norecovery,replace

restore database @db1
from disk=@path1
with stats=10,recovery,replace
end

else if @type2='L'
begin

restore database @db
from disk=@path
with norecovery,replace

restore database @db1
from disk=@path1
with stats=10,norecovery,replace

restore log @db2
from disk=@path2
with stats=10,recovery,replace
end

else
print'operation failed'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-11 : 16:07:30

What error are you getting?

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

honey_191
Starting Member

49 Posts

Posted - 2008-02-11 : 18:03:10

I am not getting any errors.I take the log backup but it is restoring the full backup.Is any thing wrong in my code?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-11 : 18:43:44

Have you checked that the correct values are being put into your variables? Run PRINT statements for each of the variables directly after the SET statements.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources