SQL Server Performance Forum – Threads Archive
Full and Differential Backup
Hi all , Is there any way to restore a differential backup when the database is running means its online. Means i restored the full backup with recovery, and after 3 days , if i get a request to restore differential backup also. Do, i have to again restore full bak with norecovery and then again restore differential bak with recovery. Thanks. Najeed.yes, you have to restore the full backup again, with norecovery, then restore the differential.
You can’t restore differential and/or tlog backups when the db is restored with RECOVERY.
If you want to read the database after full restore then restore the database with STNDBY option which will allow you to read the data…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
hi mohammed,
What u said is correct for Tran log backup but for diff backup i think we can restore it normally even if the DB is in recovery state.Pls correct me if i am wrong. Regards
Deepak
SQL DBA
Yes for the database backup and but not for the transaction log backups, refer to RECOVERY & BACKUP topics under books online. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by Deepak Rangarajan
hi mohammed,
What u said is correct for Tran log backup but for diff backup i think we can restore it normally even if the DB is in recovery state.Pls correct me if i am wrong. Regards
Deepak
SQL DBA
What u said is correct for Tran log backup but for diff backup i think we can restore it normally even if the DB is in recovery state.Pls correct me if i am wrong. Regards
Deepak
SQL DBA
May be I am missing something here…
You can’t restore the diff and/or tlog backups on top of a database restored with "WITH RECOVERY" option. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hi Mohammed,
Like u said , restore the database in standby mode, i did that.
Because, my requirement was, i was getting a full bak file and then after 3 days i was getting diff bak file. But, in the mean time, they want the database to be up, atleast for reading. Thanks alot. Mohammed Najeeduddin.
Repeat the process of full backup once again with norecovery and then restore the diff backup with recovery. Thats the only way.
Any other work around would probably complicate this simple process. Or try making use of database snapshots if you dont feel like repeating the process once again and this should be an option for somebody to access the data in a database. Satya
quote:Originally posted by najeed_dba
Hi Mohammed,
Like u said , restore the database in standby mode, i did that.
Because, my requirement was, i was getting a full bak file and then after 3 days i was getting diff bak file. But, in the mean time, they want the database to be up, atleast for reading. Thanks alot. Mohammed Najeeduddin.
Yes, you can restore differential backup when your db is in standby mode but make sure no one runs another full backup on the source server before the differential backup in three days otherwise it will fail.Like u said , restore the database in standby mode, i did that.
Because, my requirement was, i was getting a full bak file and then after 3 days i was getting diff bak file. But, in the mean time, they want the database to be up, atleast for reading. Thanks alot. Mohammed Najeeduddin.
Differential backup is depends on the full backup… Ex: On source server:
Full backup day1
tlogbackup day2
Diffbackup day3
Full backup day4
Diffbackup day5 Destination Server:
Restore Day1 full back – OK
Restore Day3 diff back – OK
Restore Day5 diff back – Will Fail…due to LSN breaks…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>