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.
| Author |
Topic |
|
DBA007
Posting Yak Master
145 Posts |
 Posted - 2009-06-03 : 09:06:54
|
| Dear Friends, we take a full database backup every end of the day,and differential backup for every 2hours and a transactional log backup for every half an hour,if for suppose my database is not working at 5pm how can I bring it back as working. |
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2009-06-03 : 09:27:15
|
well, personally I wouldn't bother with the differentials unless it's a large db, but if you're doing a full backup every night it seems like its a manageable size.ok, it's 5:05, your last transaction log backup happened at 4:45, and something bad happened, time to restore?first, do a final transaction log backup.then, restore the last full with the NORECOVERY optionthen restore the transaction logs in the order they were created with the NORECOVERY option EXCEPT for the last one.restore the last transaction log with the RECOVERY option and the STOPAT='<today's date> 17:00:00' where you replace the <today's date> with today's date, obviously.that should get you where you need to me.You will need to perform another full backup before you can start backing up differentials and transaction logs again.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-06-03 : 09:31:25
|
| 1. restore latest full backup, with norecovery2. restore all diff backups made after full backup, but before your point-in-time, with norecovery3. restore all transaction logs after last diff backup, except the last one before your point-in-time, with norecovery4. restore your last transaction log backup with a point-in-time specification, with recoveryI *think* you can also skip step number 2 completely but then you need to restore all transaction log backups since the full backup. I'm not 100% sure about this though. Something like this will do I think, but I have never tested it so you may need to revise:RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyFullBackup.bak' WITH NORECOVERYRESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDiffBackup1.bak' WITH NORECOVERYRESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDiffBackup2.bak' WITH NORECOVERYRESTORE LOG MyDatabase FROM DISK = 'C:\MyLogBackup1.bak' WITH NORECOVERYRESTORE LOG MyDatabase FROM DISK = 'C:\MyLogBackup2.bak' WITH RECOVERY, STOPAT = '2009-06-03 17:00:00'EDIT: You need to take one final log backup before doing all this too...- Lumbago |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2009-06-04 : 07:31:44
|
| Hello Friends, I think restoring the last Full Backup and the last Differential backup after the Full Backup with NORECOVERY,restoring the Transactional LogBackups after the last Differential Backup is enough,is it necessary to restore all the Differential Backups. |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2009-06-04 : 08:34:30
|
no, just the last one.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2009-06-04 : 08:49:27
|
| Dear Friend, You Mean I have to take the Transaction log Backup when the Database Fails,and restore the last full backup with NORECOVERY and the last Differential Backup with NORECOVERY and restoring the Transactional Logs in a sequence which are taken after the last differential Back and for the last Transaction log I have to specify WITH RECOVERY by Specifying the time,AM I right. |
|
|
|
|
|
|
|
|
Database Restore