Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL backups without the .BAK extension do not restore

Posted on 2013-11-26
6
Medium Priority
?
2,820 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 23

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

671 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