Solved

SQL backups without the .BAK extension do not restore

Posted on 2013-11-26
6
2,253 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 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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