restoring a backup

Question

restoring a backup

sql_2005_fan

SSCommitted

Points: 1871
More actions
December 12, 2006 at 6:21 am

#193440

Hi,
I have taken a full backup on 11/1/2006 and the file name is dbback1 and after that I have been taking differential backups everyday at 1pm and 8 pm.
But on 11/21/2006 I took a full backup dbback2. but on 11/28/2006 I have deleted some of the data in a table tbl1. but I need to restore the data. So i tried to restore my database.
As I took the fullbackup dbdback2 on 11/21/2006 My backup file dbback1 is invalid.
however I took another full backup on 11/29/2006 as dbdback3.
So is there anyway I can restore the data I have deleted on 11/28/2006.

Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Mark Yelton SSCommitted Points: 1570 More actions · Answer 1

So are you saying you have tried to restore the full backup from 11/21 and the differentials prior to your delete on 11/28 and the data is not there?

Mark

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Have you continued to do the diff backups every day at 1 and 8pm?

If you have, then you should be able to restore your full backup from 20061121, and after that the diff that is closest, but not after, when you deleted data from tbl1 at 20061128.

If you have used the same file - dbback1 - also for all diff backups, then you need to specify the correct diff backup from those that are contained in the file, you can't use the first one since it probably will be the wrong time.

You can use RESTORE HEADERONLY to see what's in your dbback files.

/Kenneth

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 3

I am restoring the full backupbackup that was taken on 11/21/2006 and then trying to apply the differential backup of 11/1/2006.

I am getting the following message.

Cannot apply the backup on device E://....dbback1 to database DB1. Restore database is terminated abnormally.

Thanks.

Mark Yelton SSCommitted Points: 1570 More actions · Answer 4

You're trying to apply a differential backup that was taken prior to the full backup you've restored. You can't do that, they're out of sequence.

HTH

Mark

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 5

No, I am trying to do the following.

First restoring the fullback taken on 11/21/2006 and then trying to apply the differential backups of the backup taken on 11/1/2006 starting from 11/21/2006 in stead of 11/1/2006.

Mark Yelton SSCommitted Points: 1570 More actions · Answer 6

That's what I'm hoping to get across to you. You can't apply any differential backup that was taken prior to the full backup you've restored from 11/21. As long as you have differentials after you're 11/21 full backup, you can recover your data prior to your 11/28 delete.

Order of operations:

Restore your full backup from 11/21

Restore only differentials taken AFTER the full backup from 11/21.

If I'm not understanding, then I apologize. But from what you are describing, you can't do what you are trying to do.

Mark

SQLBill SSC Guru Points: 51440 More actions · Answer 7

When you did a full backup on the 21st, that 'broke' the differential chain and restarted it. Any diffs you made before the full backup on 11/21 belong to and only to the backup on 11/1. Any differentials you made after the full on 11/21 belong to the 11/21 backup chain - until the 11/29 when you did ANOTHER full backup.

One problem, you aren't doing any transaction log backups, so you cannot restore to a 'point-in-time', it's all or nothing.

You deleted something on 11/28 (what time?). You need to restore the full backup from 11/21 and any differentials made AFTER that and BEFORE the delete. You probably will lose some data. Let's say your last diff was 11/28 at noon and your delete happened at 1 PM. You will lose 1 hour of data (there's no backup to cover noon to 1 PM). The next diff backup includes the delete.

-SQLBill

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 8

backupdiskfile:penMedia:Backup device d:\MSSQL\BACKUP\database1_00_08

Actually I am trying to restore the database backup from folder E.But the the restoration process is trying to restore backupdiskfile from drive D and its giving the error the file could not be found.

So how can I restore the dabase from only the backups from the folder E.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

You will need to script the backup and specify in the SQL where to find the backup file you are using to restore. Also, You only need to restore from the latest differential file prior to when the data was dleted. Each differential backup contains all database changes since the last full backup. Be sure to use the NORECOVERY option when restoring the Full Backup.

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 10

HEre is what I am doing.

So far I tried to restore the database from enterprise manager.

Now started restoring it from query analyzer.

The first step is restoring the fulllback taken on 11/21/2006 with NORECOVERY option. I am restoring the database db1 as restore_test_db.

RESTORE DATABASE restore_test_db FROM DISK = 'E:\MSSQL\BACKUP\db1_backup_11212006'

WITH MOVE 'db1_Data' TO 'D:\MSSQL\Data\restore_test_db_data.MDF',

MOVE 'db1_Log' TO 'D:\MSSQL\Data\restore_test_db_Log.MDF',

NORECOVERY

After that how can i restore my differential backup.

MY differential backup was taken onto the following location.

E:\MSSQL\BACKUP\db1_backup_11012006

and it has differential backups starting from 11012006 until 12032006.but I need to restore the differenital backups from 11212006 to 11282006( the day I have deleted the data) to the database restore_test_db.

Thanks to all of you guys for all the help.

SQLBill SSC Guru Points: 51440 More actions · Answer 11

Question: On the 28th, what happened first?

Did the Diff backup happen and then the delete? Or was it the delete then the backup?

If the delete happened before the backup, then you can NOT use the 11/28 differential.

What is the name of the differential backup files? This (db1_backup_11012006) can't be it as that is the name of the full backup.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 12

BTW - you just use the same command as for the full backup. But change it to WITH RECOVERY.

The only difference is you have to 'point' it to the differential backup instead of the full backup.

Update: Also, remember as said before, you only need to restore ONE differential. The most recent one that does NOT have the delete.

-SQLBill

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 13

First, you need to know the file position of your 11282006 differential backup from your 'E:\MSSQL\BACKUP\db1_backup_11012006' backup file. Use

RESTORE HEADERONLY from disk = 'E:\MSSQL\BACKUP\db1_backup_11012006'

Take nore of the POSITION number of your differential backup for 11282006.

Restore your FULL backup as you did, then restore your differential backup as follows.

RESTORE DATABASE restore_test_db FROM DISK = 'E:\MSSQL\BACKUP\db1_backup_11012006'

WITH MOVE 'db1_Data' TO 'D:\MSSQL\Data\restore_test_db_data.MDF',

MOVE 'db1_Log' TO 'D:\MSSQL\Data\restore_test_db_Log.MDF',

RECOVERY, FILE = 28 -- use the position number of your 1128 diff backup here

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"