Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Restore error message 1

Status
Not open for further replies.
phil22046

phil22046

Programmer
Sep 12, 2001
154
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
is there any potential conflict between my t-log backups and my differential backups?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
So you restored the Full Backup, then a Differential, and then the log backups made after that differential???

-SQLBIll

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

torquinian
  • Locked
  • Question Question
Error 0x80131904 when trying to connect to database
Replies
0
Views
721
torquinian
torquinian
intel233
  • Locked
  • Question Question
Backup/Restore DB from SQL issues
Replies
2
Views
447
intel233
intel233
DougNaf
  • Locked
  • Question Question
SQL 2016 - DB attachment and error 3415
Replies
1
Views
649
fredericofonseca
fredericofonseca
dd2maker
  • Locked
  • Question Question
SQL server 2008 R2 backup restore issue
Replies
2
Views
403
dd2maker
dd2maker
TheBugSlayer
  • Locked
  • Question Question
Backu and restore of multiple filegroups
Replies
2
Views
390
TheBugSlayer
TheBugSlayer

Part and Inventory Search

Sponsor

Back
Top