SQL backups without the .BAK extension do not restore
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:
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!
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 0I 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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).ASKER
thanks duttcom, I will try that on SQL2012 since that's the next one on my list of restores
Right click on the databases container and select Refresh ?
ASKER
I wish I could give you double points for pointing out my stupidity.
Thank you so much for your help!
Thank you so much for your help!
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.