Solved

SQL backups without the .BAK extension do not restore

Posted on 2013-11-26
6
1,960 Views
Last Modified: 2013-11-26
I am running into a strange problem with my backups and restores.  I can't find much (any) information online.

When I backup a database from either a SQL2K or SQL2K5, the files are created without a .BAK extension.  These are two seperate servers backing up different databases to different locations.

When I try to restore either of these backup files (by adding the .BAK extension to them first), to either a SQL2K5 server or a SQL2012 server (depending on which version I am restoring from), I get errors.

For example, here is once instance of an error:
RESTORE DATABASE MartinFletcherCorporate FROM DISK = 'E:\sqlshare\MartinFletcherCorporate.BAK'
WITH MOVE 'MartinFletcherCorporate_Data' TO 'E:\SQLData\MartinFletcherCorporate_Data.mdf',
MOVE 'MartinFletcherCorporate_Log' TO 'E:\SQLData\MartinFletcherCorporate_Log.ldf'
GO

Msg 3234, Level 16, State 2, Line 1
Logical file 'MartinFletcherCorporate_Data' is not part of database 'MartinFletcherCorporate'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


--------------------------------------------

RESTORE FILELISTONLY FROM DISK = 'E:\sqlshare\MartinFletcherCorporate.BAK'



Martinfletcher_dat         E:\Program Files\Microsoft SQL Server\MSSQL\Data\martinfletcher.mdf              D                PRIMARY             3459121152         35184372080640               1              0              0              00000000-0000-0000-0000-

000000000000    0              0              0              512         1              NULL     267267000000004600003               7E3EF7AB-309E-428E-A638-F96C95477C29             0              1
Martinfletcher_log          E:\Program Files\Microsoft SQL Server\MSSQL\Data\martinfletcher_log.ldf        L                NULL     1048576                35184372080640               2              0              0              00000000-0000-0000-0000-

000000000000    0              0              0              512         0              NULL     0              00000000-0000-0000-0000-000000000000                0              0

Open in new window


I don't know how to pinpoint the problem since it's occurring on three different servers running three different versions of SQL.

Any ideas?

Thanks!
0
Comment
Question by:fabi2004
  • 3
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
Comment Utility
You are telling it to move the following:

MartinFletcherCorporate_Data
MartinFletcherCorporate_Log

The RESTORE FILELISTONLY says:

Martinfletcher_dat
Martinfletcher_log

Try changing your restore statement to use those two file names instead of the ones you're using now and it should work.

You can always rename them after the restore.
0
 
LVL 12

Expert Comment

by:duttcom
Comment Utility
I have no problems restoring from a file without the bak extension.

Leave the files exactly as they are, with no extension, then when you choose the source file to restore from, make sure you select All files(*) from the "Files of type" dropdown at the bottom of the Locate Backup file window. Then you can select your file and the restore should complete.

*Edit - this is based on using SQL management studio to perform the restore.
0
 
LVL 1

Author Comment

by:fabi2004
Comment Utility
Perfect!  No errors restoring to SQL2K5.  But shouldn't I see it listed in the databases container now?

Processed 395992 pages for database 'MartinFletcherCorporate', file 'Martinfletcher_dat' on file 1.
Processed 1 pages for database 'MartinFletcherCorporate', file 'Martinfletcher_log' on file 1.
Converting database 'MartinFletcherCorporate' from version 539 to the current version 611.
Database 'MartinFletcherCorporate' running the upgrade step from version 539 to version 551.
Database 'MartinFletcherCorporate' running the upgrade step from version 551 to version 552.
Database 'MartinFletcherCorporate' running the upgrade step from version 552 to version 553.
Database 'MartinFletcherCorporate' running the upgrade step from version 553 to version 554.
Database 'MartinFletcherCorporate' running the upgrade step from version 554 to version 589.
Database 'MartinFletcherCorporate' running the upgrade step from version 589 to version 590.
Database 'MartinFletcherCorporate' running the upgrade step from version 590 to version 593.
Database 'MartinFletcherCorporate' running the upgrade step from version 593 to version 597.
Database 'MartinFletcherCorporate' running the upgrade step from version 597 to version 604.
Database 'MartinFletcherCorporate' running the upgrade step from version 604 to version 605.
Database 'MartinFletcherCorporate' running the upgrade step from version 605 to version 606.
Database 'MartinFletcherCorporate' running the upgrade step from version 606 to version 607.
Database 'MartinFletcherCorporate' running the upgrade step from version 607 to version 608.
Database 'MartinFletcherCorporate' running the upgrade step from version 608 to version 609.
Database 'MartinFletcherCorporate' running the upgrade step from version 609 to version 610.
Database 'MartinFletcherCorporate' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 395993 pages in 70.981 seconds (45.701 MB/sec).

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:fabi2004
Comment Utility
thanks duttcom, I will try that on SQL2012 since that's the next one on my list of restores
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
Right click on the databases container and select Refresh ?
0
 
LVL 1

Author Closing Comment

by:fabi2004
Comment Utility
I wish I could give you double points for pointing out my stupidity.

Thank you so much for your help!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now