March 3, 2010 at 6:23 am
Dear All
I have some doubts about restore of differential backup.
1. Is it necessary that for restore differential backup, database must be in restore mode?
2. restore mode status generated by restore a full backup using NO RECOVERY.
is any other technique available which can we bring database in a suitable mode for restore a differential backup.
3. At This Time When We Want To Restore Differential Backup then every time we restore two backup file first full backup and then differential backup file.It Is Write Way?
Thanks In Advance.
March 3, 2010 at 6:38 am
before restoring a differential backup you must first restore a full backup with the norecovery option.
This is the only way to do it.
note: differential backups are cumulative, so if you have a full backup followed by 3 differential backups, you only have to restore the full backup and the last differential.
---------------------------------------------------------------------
March 3, 2010 at 10:00 pm
Suppose We have Following Situation
SERVER 1
1. Take Full Backup of Main Server. Date 2010-03-01
2. Take Differential Backup of Main Server. Date 2010-03-02
3. Take Differential Backup of Main Server. Date 2010-03-03
SERVER 2 Now We Want To Restore
We Follows These Step.
1. Restore Full Backup Of Date 2010-03-01 With No Recovery Mode.
2. Restore Differential Backup Of Date 2010-03-01.
Now SERVER 2 Database Has Data Till 2010-03-02.
If We Want To Restore differential Backup of 2010-03-03 . Then Is it necessary that we again restore full backup of date 2010-03-01 and then Differential Backup of Date 2010-03-03 ?
Can We Restore Differential Backup of Date 2010-03-03 with out restoring full backup of Date 2010-03-01 in any way ?
March 4, 2010 at 3:20 am
to restore the diff backup you should restore full backup first.
but in your case if you've already installed day 1 full backup and then
restored day 2 diff backup on top of it with either norecovery or standby mode
then you can restore the day 3 diff on top of all without restoring full backup again, but the restriction remains the same the last backup which was restored should have put the DB in either NORECOVERY or STANDBY MODE.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 4, 2010 at 4:43 am
then you can restore the day 3 diff on top of all without restoring full backup again, but the restriction remains the same the last backup which was restored should have put the DB in either NORECOVERY or STANDBY MODE.
It Means Database can not in Usable mode after restore differential backup in NO RECOVERY or STANDBY MODE.
Is any Other Way For Bring Database In NO RECOVERY?
March 4, 2010 at 4:47 am
if you want to bring a database online use 'restore database dbname with recovery'
the only way to put a database in norecovery is to run a restore with that option.
---------------------------------------------------------------------
March 4, 2010 at 4:54 am
Thanks For All For Participates and Clarify Some Issue.
March 4, 2010 at 4:59 am
vbprogrammer1986 (3/4/2010)
then you can restore the day 3 diff on top of all without restoring full backup again, but the restriction remains the same the last backup which was restored should have put the DB in either NORECOVERY or STANDBY MODE.It Means Database can not in Usable mode after restore differential backup in NO RECOVERY or STANDBY MODE.
Is any Other Way For Bring Database In NO RECOVERY?
No
you can put the DB in readonly mode by selecting STANDBY Mode.
i think you should refer this link
NORECOVERY
Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).
Note When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
RECOVERY
Instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use.
If subsequent RESTORE operations (RESTORE LOG, or RESTORE DATABASE from differential) are planned, NORECOVERY or STANDBY should be specified instead.
If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
STANDBY = undofilename
Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.
The same undo file can be used for consecutive restores of the same database. For more information, see Using Standby Servers.
Important If free disk space is exhausted on the drive containing the specified undo file name, the restore operation stops.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply