Restore error message 1

[phil22046]

I created a new Database this morning for testing restores.
It is S030_B. The logical files are S030_B_Log and S030_B_data - default values.

For an hour I have been trying to restore to this database.

When I try to restore from a differential backup, I get the following error message:

Logical file 'S030_B_Log' is not part of database 'S030_B'. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally

 

[SQLBill]

Okay, you don't have any log backups. 1 equates to a FULL Backup and 5 to a Differential Backup. You need to restore the Full backup and only one (usually the most recent) Differential Backup.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

The full backup restores just fine. The differential backups won't restore at all. And I am carefull to make sure that I select the option that allows transaction logs to restore after the full backup is finished. I do transaction log backups to individual files with the extension .TRN

 

[phil22046]

I am restoring from S030Diff onto another server and the file locations are different. Although the error messages I get don't indicate what the problem is I am suspecting that this might be somehow related.

 

[SQLBill]

So, are you leaving the backup in it's original location and trying to restore from there to the new server? If so, move the backup file to the new server and try the restore.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

The backup location is on the new server.

This is the command I am using for the Full restore

RESTORE DATABASE [S030F] FROM [S030Diff]
WITH FILE = 1,
NORECOVERY,
NOUNLOAD,
STATS = 10,
REPLACE,
MOVE 'S030_Data' TO 'D:\MSSQL\MSSQL\Data\S030F_data.mdf',
MOVE 'S030_Log' TO 'D:\MSSQL\MSSQL\Data\S030F_log.ldf'
GO

 

[phil22046]

This is the command I tried to use to restore the last differential backup:

RESTORE DATABASE [S030F] FROM [S030Diff]
WITH FILE = 8,
NOUNLOAD,
STATS = 1,
REPLACE,
MOVE 'S030_Data' TO 'D:\MSSQL\MSSQL\Data\S030F_data.mdf',
MOVE 'S030_Log' TO 'D:\MSSQL\MSSQL\Data\S030F_log.ldf'
GO

and the resulting error messages

Msg 3136, Level 16, State 0, Line 2
Cannot apply the backup on device 'S030Diff' to database 'S030F'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

 

[SQLBill]

For the diff, try taking out the REPLACE. That was done with the Full backup and shouldn't be needed again.

I also don't believe you need the MOVE statements when restoring the diff as you already did that with the Full backup restore.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

I changed the restore command to this:

RESTORE DATABASE [S030F] FROM [S030Diff]
WITH FILE = 9,
NOUNLOAD,
STATS = 1
GO

and got this result:
Msg 3136, Level 16, State 0, Line 1
Cannot apply the backup on device 'S030Diff' to database 'S030F'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

[phil22046]

I did a restore Header only from the S030Diff device. The LSN numbers don't match from the full backup and even between the 8 differential backups. SO I will eliminate the extra full backup that runs at 9:00pm every day that I inherited from my predecessor and do a new Full backup overwriting the S030Diff device. And keep the every 1/2 hour t-log backups and the scheduled differential backup every night at 12:30 am.

 

[phil22046]

is there any potential conflict between my t-log backups and my differential backups?

 

[SQLBill]

Depends on what you mean by conflict. Transaction Log backups are 'tied' to the previous Full backup or Differential backup. So, once a differential backup is made, the follow log backups 'belong' to that differential.


-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

maybe I should eliminate the differential backup then?

 

[phil22046]

Well back to the beginning.

The restore will not work.

Microsoft SQL-DMO(ODBC SQLState: 42000)
Cannot apply the backup on device '\\sh-case2\s030backup\s030db_differential' to database 'S030F'
RESTORE DATABASE is terminating abnomally

 

[SQLBill]

Okay, first why are you creating a new database and then restoring to that database? Just restore the backup files, the database doesn't need to already exist.

Next, make sure that the only full backups made exist in your backup file. There shouldn't be any other full backups made at any time anywhere else or that will mess the whole thing up.

That said, restore the most recent full backup using WITH NORECOVERY. Then find the most recent differential made AFTER that full backup and restore it WITH RECOVERY.

-SQLBill

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

I am attempting to restore to a different server, the one that will be used as the disaster recovery machine. I thought to restore to a different location you had to create an empty database to restore into.

 

[phil22046]

There are no 'Full' backups running. I am just running the differential every 24 hours and the t-log backups every 1/2 hour. Yesterday I started over from scratch and ran a 'Full' backup overwriting the file that was used for the incrental backups and I also disabled the Full backup that was running at 9PM every night.

 

[phil22046]

Can you give me a simple set of backup and restore strategies that I can test and then implement? I can use the northwind database for testing. I just want to finally succeed with this disaster recovery plan I am trying to set up.

 

[SQLBill]

All restores require one full backup at a minimum. That is what creates the database. Then differentials and/or log backups can be restored to the full backup that was restored.

Depending upon database sizes...

You want to do a full backup on a schedule, whether it is once a month, once a week, or once a day. The full backups make it easier to do a restore. For example, if you are doing a full backup on the first of the month and your database fails on the 25th, you need to restore the full backup from the 1st, the most recent differential (if you are doing them) and any tlogs since the differential. Now, if you are doing differentials daily, a monthly full backup isn't a big problem.

You want to consider doing differentials as they will decrease the amount of tlogs you need to restore. I wouldn't do more than one differential a day. If you are doing a full backup daily, you probably won't need differentials (however, on a critical database I was doing a full backup at midnight, a differential at noon and tlogs every hour).

Log backups. These are IMPORTANT and a must if your data is critical. Log backups are the only ones that can be restored to a 'point-in-time'. Lets say you are doing hourly log backups. Someone deletes a table at 1:15 PM. You would immediately do a log backup, then restore the full backup (with norecovery), any differential before the 1:15 Pm time (again with norecovery), then any log backup since the differential or full backup if there isn't a differential. Lastly, you restore the final log backup using WITH STOPAT and stopping the restore at a time prior to the delete.

Considerations: each differential backup contains ALL of the changes made since the last full backup. So, if you only do a full backup on the 1st of the month and differentials daily, a differential on the 25th has all the changes from the 1st to the 25th and that file could be quite large. Also, ANY full backup 'breaks the chain' of backups and restarts them. Let's say you have a schedule of: FULL once a week on Sunday, Differential once a day, Log backups hourly. Then on Wednesday, you or someone else does a full backup to move to a test server. Now the differentials and log backups made after Wednesday's full backup can not be applied to the Sunday backup. So all the differentials and log backups made after Wednesday's full backup belong to that full backup and can only be restored to that full backup. If you do not save, or are not aware of the Wednesday's full backup, then restores may fail if you restore Sunday's Full and Thursday's differential for example.

Hopefully that is clear. If not, let us know.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

I have been attempting to do differential backups and T-log backups. The Differential backups never will restore. The T-log backups do restore ok.

What seems to be happening is that the T-Log and Differential backups conflict.

 

[SQLBill]

So you restored the Full Backup, then a Differential, and then the log backups made after that differential???

-SQLBIll

Posting advice: FAQ481-4875