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

 

[ptheriault]

you have to restore the full database first, then any differential backups, then any log backup. It looks like from the error message you are trying to restore the log backup first. If that is all you have in your backup set it may suggest that you are missing the full database backup from your media.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

The differential database backup was first started with a full backup and later every 24 hours a differential backup is appended to the existing file.

So I think I am doing what you instructed me to do. I am attempting the restore of File 1 from the differential backup. And I still get that error.

 

[ptheriault]

Phil,
I think there might be some confusion here.
You need to run the full database backup first. Then your diffs or Tlog backups.

To restore a database you would restore in the same order.

Restore full backup first with norecovery, then apply diffs or tlog backups.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here is a typical schedule.

Monday
12 AM FULL Database Backup

Between 1 AM and 11 PM hourly Tlog backups

Tues
12 AM Differential Backup
Between 1 AM and 11 PM hourly Tlog backups

Wed
12 AM Differential Backup
Between 1 AM and 11 PM hourly Tlog backups

Continue till Monday when everything starts over again.


Now to restore a database.

You would first apply mondays backup with no recovery
Then apply you most recent differential with no recovery
Then apply any tlogs till you get the database to the most current state.

Does this make sense?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

One more thought.
Don't put your Tlog backups in the same device as your Full or Differential backups.

So you will have one device for Data and one for Logs.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

Here is how I set up my backups.

First I did a manual full backup to a file named S030Diff

Next I set up a backup job to do a differential backup at 12:30am every day and append it to the S030Diff backup file.

So when I do a restore, this file has File 1,2 and 3 that I can pick for the restores. Since I set this up on tuesday and today is thursday, I assume that File 1 is the Full backup, File 2 is the first differential from 12:30 Wednesday and File 3 is the second differential from 12:30 am today.

If I am wrong in my assumptions please correct me.

 

[ptheriault]

That sounds correct.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

And you should have RESTOREd FILE 1 and then FILE 2.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Excuse my last post, it should have been:

And you should have RESTOREd FILE 1 and then FILE 3.

-SQLBill

Posting advice: FAQ481-4875

 

[phil22046]

When I try to restore File1 I get that error messgae that says:

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

 

[phil22046]

When I restore, should I choose Database Complete or Database Differential? I have been choosing Database Complete.

 

[ptheriault]

You would do complete first with no recovery then do you diff with no recover

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I wasn't doing it through the query window, I was doing this by right clicking--> All Tasks --> Restore

 

[ptheriault]

That's ok, you can still do them a separate restores. Just select the check box to leave the database in the restoring state so you can appy more backup sets.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I tried again and got the same 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

So I deleted S030_B and created S030_C

I tried the restore and got these messages:

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

 

[ptheriault]

That puts us back to where started doesn't it?

USe the RESTORE FILELISTONLY option to see what backup sets are in your restore file.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I am not sure how to use the restore filelistonly, I get this message when I try it.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FILELISTONLY'.

 

[ptheriault]

You syntax should look something like this.

RESTORE FILELISTONLY FROM AdventureWorksBackups

If this is a device you can also right clickon the device in EM and select view contents.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

I figured it out. I was thinking I need to change the logical name as well as the physical name not realizing that the logical name referred to the data in the backup and the Physical name was where the data was to end up.