Solved

SQL backups without the .BAK extension do not restore

Posted on 2013-11-26
6
2,049 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
ID: 39679535
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
ID: 39679538
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
ID: 39679547
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:fabi2004
ID: 39679550
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
ID: 39679557
Right click on the databases container and select Refresh ?
0
 
LVL 1

Author Closing Comment

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

Thank you so much for your help!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

914 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

17 Experts available now in Live!

Get 1:1 Help Now