December 12, 2011 at 2:49 am
Good day all.
Please let me know if this is possible.
i have a server that has some faulty hardware, it just shuts down after running smoothly for a few hours.
We decided to move to another server.
I restored a backup to the new server ( norecovery) .
I have some differential backups that i can restore, but i do not have the latest differential backup.
Can i restore one of the latest older backups to the new server while i take a latest backup on teh faulty server.
That way i reduce the time to get the new server up and running to move to production.
So if the current differential backup fails due to the server shutting down , then i continue with the TL backup that i have after the latest differential. But then i have about 30 TL backups to restore , (time consuming)
other alternative :if the current differential backup doesnt fail and complete, then can i take that differential backup and restore after the first one was already done with no recovery. meaning can i restore 2 differentials instead of 1 differential and many TL backups.
Thank you
Imtiaz Mohamed
December 12, 2011 at 3:16 am
This is what you should follow:
•FULL Restore with no recovery
•Differential Restore (most recent, available) with no recovery
•Log Restores (in proper sequence) with no recovery
•Recovery.
December 12, 2011 at 3:32 am
Sorry i think you are misunderstanding my request.
Due to time constarints,
i would like to take a diff backup on the faulty server at the same time while the new server is being restored with the latest differential. the faulty server is in production and is teh live server.
The restore of the differential would take approx 2 hours to complete and taking a new differential on teh faulty would also take 2 hours to complete.
So to avoid wasting time. can i restore the last diff, then if the fauty server hasnt failed , can i then restore the last diff that was just done, so restore of 2 differentials. instead of doing the TL restored (i have +- 30 of them).
thanxs.
December 12, 2011 at 3:39 am
imtiazm (12/12/2011)
Sorry i think you are misunderstanding my request.Due to time constarints,
i would like to take a diff backup on the faulty server at the same time while the new server is being restored with the latest differential. the faulty server is in production and is teh live server.
The restore of the differential would take approx 2 hours to complete and taking a new differential on teh faulty would also take 2 hours to complete.
So to avoid wasting time. can i restore the last diff, then if the fauty server hasnt failed , can i then restore the last diff that was just done, so restore of 2 differentials. instead of doing the TL restored (i have +- 30 of them).
thanxs.
Not Sure. Never tried it & will recommend you not to try as well.
Per my understanding you can restore only one differential (may be another one with overwrite, never tried it). Then you should restore logs in sequence.
It’s something you shouldn’t play with, absolutely NOT in PROD server.
December 12, 2011 at 3:48 am
OK solution reached.
Faulty server just rebooted itself again,
So i did not get a successful differential off the live server.
So ill go with teh normal way of restoring full, diff and TL's.
but i think just so others have the option when seaching this topic in future,
Can someone please reply if multiple differentials can be restored. then make the latest with recovery
Thank you
December 12, 2011 at 4:22 am
Sure, as long as they're all WITH NORECOVERY and no new full backup was taken anywhere in the 'chain'. Generally one wouldn't do that, they'd restore the last diff and then take log backups and restore them rather than another differential to bring a new server up to date, but it'll work fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2011 at 4:26 am
Please help or guide in this problme, never had this before.
While restoring all teh trasnaction logs, i came to a point on friday when the server had an unexpected shutdown.
Msg 824, Level 16, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7d7c9874; actual: 0xb0e797f0). It occurred during a read of page (3:88440991) in database ID 7 at offset 0x0000a8b013e000 in file 'Y:\Data\media2.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
1) I Cant run a DBCC cos the DB is still in a nonrecovered state
2) Should I continue with the restores of the other transaction logs i have ? Will it be possible.
or do i need to start from scratch with my restore and stop at teh point prior to teh failure,
but then what about all teh data after the failure point, how do i restore this.
Thanx for any help, much appreciated.
Imtiaz Mohamed (DBA)
December 12, 2011 at 5:49 am
Please post new questions in a new thread. Post under one of the data corruption forums. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 7:02 am
1) full backup- option with replace with existing database and recovery with norecovery:-
RESTORE DATABASE [diff_test] FILE = N'diff_test' FROM DISK = N'G:\dinesh\diff_test_full.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
2)differetial backup with norecovery:-
RESTORE DATABASE diff_test
FROM DISK = 'G:\dinesh\diff_test_diff1.bak'
WITH RECovery;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply