Home > SQL > RESTORE DATABASE is terminating abnormally

RESTORE DATABASE is terminating abnormally

Problem

While try to restore the database using *.bak file, SQL throws an error message that Restore Database is terminating abnormally.

I got the below error while I try to execute the T-SQL “Restore database <DatabaseName> from disk='<Full Path>\<DatabaseName>.bak’

Msg 3234, Level 16, State 2, Line 1

Logical file ‘XXXXX’ is not part of database ‘XXXXXXX’. Use RESTORE FILELISTONLY to list the logical file names.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Again I try to restore with different T-SQL statement

Restore database <DatabaseName> from disk=’<Full Path>\<DatabaseName>..bak’ with

move ‘<Logical PrimaryFileName >’ to ‘<Full Path>\< PrimaryFileName >.mdf’,

move ‘<Logical SecondaryFileName>.’to ‘<Full Path>\< SecondaryFileName>.ndf’,

move ‘<LogicalLogName>’ to ‘<Full Path>\< LogName>.ldf’

Again I Got the same exception as above.

Workaround

While I try to fix this error SQLTEAM forum give me detail for the exception.

The RESTORE FILELISTONLY option allows you to see a list of the files that were backed up.  So for example if you have a full backup you will see all of the data files (mdf) and the log file (ldf).

The RESTORE FILELISTONLY can be use with the backup file (*.bak), that exist on the disk. If you have multiple backup files in a single file then you have to use “WITH FILE = X”

Reason

I executed T-SQL statement RESTORE FILELISTONLY FROM DISK = ‘<Full Path>\<BackupFileName>.Bak’

Output as below:

Then I found out that I wrongly specified <Logical PrimaryFileName >,<Logical SecondaryFileName>,<LogicalLogName>.

<Logical PrimaryFileName >=’LogicalPrimaryFileName’must same as specified in ‘Logical’ column of FILELISTONLY output

<Logical SecondaryFileName>=’Logical SecondaryFileName’ must same as specified in ‘Logical’ column of FILELISTONLY output

<LogicalLogName>=’LogicalLogName must same as specified in ‘Logical’ column of FILELISTONLY output

After I wrote T-SQL Query as below

Restore database <NewDatabaseName> from disk=’<Full Path>\<DatabaseName>..bak’ with

move ‘<LogicaNameOfPrimaryFileGroup in .bak>’ to     ‘<Full Path>\< PrimaryFileName >.mdf’,

move ‘<LogicalNameOf SecondaryFileGroup in .bak >.’to ‘<Full Path>\< SecondaryFileName>.ndf’,

move ‘<LogicalNameOfLog in .bak>’ to ‘<Full Path>\< LogName>.ldf’

Now I can able to successfully restore the database.

Advertisements
Categories: SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: